I have a worksheet with six different conditional formats applied to various columns and rows. All conditional formatting is based on formulas. However, I find that as I insert rows to accommodate data entry, over time it sort of messes up my conditional formatting and I need to periodically delete all conditional formatting and create the rules again. In fact, to facilitate this I documented the rules (as shown below) so I can easily recreate them.
My question is, how can I do this as a macro to 1) remove all conditional formatting then 2) recreate the rules as shown?
RULE 1 (GREEN FILL) - COL A (A2:A1000)
=AND($O2>0,$P2>0,NOT(ISBLANK($B2)),$B2>=TODAY())
RULE 2 (YELLOW FILL) - COL H (H2:H1000)
=AND(NOT(ISBLANK(I7)),OR(ISBLANK(H7),H7=0,H7=""))
RULE 3 (RED FILL) - COL J (J2:J1000)
=AND(ISBLANK(J2),O2>0,O2<>"",P2>0,P2<>"")
RULE 4 (RED FILL) - COL Q (Q2:Q1000)
=AND((Q2-TODAY())<=8,R2<>0)
RULE 5 (GREY FILL) - ALL ( A2: R1000)
=AND(NOT(ISBLANK($B2)),$B2<TODAY())
RULE 6 (TOP BORDER) - ALL ( A2: R1000)
=AND($A2<>"",$A2<>$A1)
using code to apply conditional formatting
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: using code to apply conditional formatting
It would look like this; you'll have to finish it yourself.
Note the use of double double quotes within the formula strings.
Code: Select all
Sub RecreateFormatConditions()
' Remove conditional formatting
Range("A2:R1000").FormatConditions.Delete
' Rule 6
With Range("A2:R1000").FormatConditions.Add _
(Type:=xlExpression, Formula1:= _
"=AND($A2<>"""",$A2<>$A1)")
.SetFirstPriority
With .Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
.StopIfTrue = False
End With
' Rule 1
With Range("A2:A1000").FormatConditions.Add _
(Type:=xlExpression, Formula1:= _
"=AND(SO2>0,SP2>0,NOT(ISBLANK($B2)),$B2>=TODAY())").Interior
.PatternColorIndex = xlAutomatic
.Color = vbGreen
End With
' Rule 2
With Range("H2:H1000").FormatConditions.Add _
(Type:=xlExpression, Formula1:= _
"=AND(NOT(ISBLANK(I7)),OR(ISBLANK(H7),H7=0,H7=""""))").Interior
.PatternColorIndex = xlAutomatic
.Color = vbYellow
End With
' Rule 3
...
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: using code to apply conditional formatting
Thanks Hans, I think I can finish it. Just curious... why did you start with rule 6? I probably should have explained that the rule numbers had no significance other than letting me know how many rules/formulas I had.
You will notice in my original post that only rules 5 & 6 apply to the entire range (A2:R1000) while the others applied only to specific columns. In the macro code, does it matter what order they are in?
Also, what does '.SetFirstPriority' mean? Should it be followed later with other references to priority (priority 2, 3, ...)?
You will notice in my original post that only rules 5 & 6 apply to the entire range (A2:R1000) while the others applied only to specific columns. In the macro code, does it matter what order they are in?
Also, what does '.SetFirstPriority' mean? Should it be followed later with other references to priority (priority 2, 3, ...)?
Don
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: using code to apply conditional formatting
I started by recording a macro. For some reason, the macro didn't play back correctly if rule 6 was at the end, so I moved it to the beginning.
The line .SetFirstPriority is a relict from the recorded macro, you can test if the macro still works correctly if you remove it.
The line .SetFirstPriority is a relict from the recorded macro, you can test if the macro still works correctly if you remove it.
Best wishes,
Hans
Hans