Excel VBA: Copying the conditional format rules from one sheet to another.
Situation
I have two worksheets: one 'SheetA' with several conditional for matting rules and one 'Sheetb' with none.
The CF rules on SheetA are custom formula based.
This is in Excel 2013
Request
I need some VBA to copy all the conditional formatting rules, inc formats / colours, from one sheet to another - can anyone suggest how I can do this ?
I have tried cycling through all the rules on SheetA and then for each adding a rule to SheetB and passing the properties of the rule in SheetA but this fails as all the arguments to the 'add' function are not directly accessible as properties of the existing rules.
Limitations
I have two worksheets: one 'SheetA' with several conditional for matting rules and one 'Sheetb' with none.
The CF rules on SheetA are custom formula based.
This is in Excel 2013
Request
I need some VBA to copy all the conditional formatting rules, inc formats / colours, from one sheet to another - can anyone suggest how I can do this ?
I have tried cycling through all the rules on SheetA and then for each adding a rule to SheetB and passing the properties of the rule in SheetA but this fails as all the arguments to the 'add' function are not directly accessible as properties of the existing rules.
Limitations
- I can't use 'copy and paste' - as there is no ' PasteSpecial' parameter that only copies CF
- I need to let the user define the conditional formatting rules so don't want to set the rules from code (which would be easy)
ASKER
Skylar: Thanks for the response. I know I don't need VBA but I have written a custom system in VBA and I need to copy the formatting using VBA.
it depends on what content to be generated...
for me, I would create a template worksheet ("SheetA"), (so you predefined the conditional formatting in this worksheet and you may hide this worksheet) and then use simple VBA Copy function to generate a copy as target worksheet ("SheetB"), and then amend the SheetB with other VBA codes when necessary.
Worksheet.Copy method (Excel)
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.copy
hope this make sense to you.
for me, I would create a template worksheet ("SheetA"), (so you predefined the conditional formatting in this worksheet and you may hide this worksheet) and then use simple VBA Copy function to generate a copy as target worksheet ("SheetB"), and then amend the SheetB with other VBA codes when necessary.
Worksheet.Copy method (Excel)
https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.copy
hope this make sense to you.
ASKER
Ryan: my system already uses the copy function you have suggested to copy sheet X from workbook 1 to workbook 2 as sheet B and I then need the VBA to copy all the conditional formatting rules ONLY from sheet A of workbook 2 to the new sheet B of workbook 2. However since Sheet X contains data I am importing and don't want to lose I can't later do a cell / range copy from sheet A to sheet B
ASKER
I have been able to achieve the same functionality by using 'range.copy' and 'range.PasteSpecial Paste:=xlPasteFormats' but this is not ideal.
Using PasteSpecial has a number of drawbacks (slower, copies over cell formatting in addition to conditional formatting, needs to have the target sheet selected to prevent issues etc..)
Surely another expert has been able to copy ONLY conditional formats from one sheet to another but explicitly recreating the AF rules on a range object ?
Using PasteSpecial has a number of drawbacks (slower, copies over cell formatting in addition to conditional formatting, needs to have the target sheet selected to prevent issues etc..)
Surely another expert has been able to copy ONLY conditional formats from one sheet to another but explicitly recreating the AF rules on a range object ?
' Modify the "Applies To" ranges
Cells.FormatConditions(1).
Cells.FormatConditions(2).
Cells.FormatConditions(1).
Cells.FormatConditions(2).
ASKER
Sorry Robberbaron I don't understand. Are you saying you can use 'ModifyAppliesToRange' to copy to another sheet because the MS page says the range has to be on the same sheet as the format condition ?
See: https://docs.microsoft.com/en-us/office/vba/api/excel.formatcondition.modifyappliestorangeMS documentation of 'ModifyAppliesToRange' function
See: https://docs.microsoft.com/en-us/office/vba/api/excel.formatcondition.modifyappliestorangeMS documentation of 'ModifyAppliesToRange' function
ok. i guess ive ever only used it on the same worksheet
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it is easy and simple with the paint brush built in feature in excel.
simply the entire range of your SheetA which currently has Conditional formatting. then double click on the paint brush.
then go to sheet two and select the range that you want the CF to be applied. once you click, the identical CF will be set in your SheetB