using code to apply conditional formatting

[dasadler]

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)

[HansV]

It would look like this; you'll have to finish it yourself.

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

Note the use of double double quotes within the formula strings.

[dasadler]

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, ...)?

[HansV]

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.