VBA Help - Create Conditional Formatting to Sheet

[Johnny Thunder]

Hello Everyone,

I am stuck on a project that requires a sheet to get a refresh to the conditional formatting to a sheet. Whenever a user Adds Rows to a sheet or duplicates the sheet the conditional formatting seems to get messed up by duplicating rules and changing my ranges from being entire Columns (example: A:A) to specific ranges (example: $A$100:$A$120).

There is about 20 Sheets in my workbook that all rely on the same Conditional Formatting on each sheet so if there is a fast way to correct this that doesn't involver VBA that would be helpful but if not, having a script that can clear all Conditional Formats and then reapply that would be great.

My current Conditions:

1. Formula: =$R1="Promo Ended" - Color Shading: Grey Cell Fill - Applies to: $Q:$S
2. Formula: =$N1="Promo Ended" - Color Shading: Grey Cell Fill - Applies to: $M:$O
3. Formula: ="Next Week" - Color Shading: Yellow Fill with Dark Yellow Text - Applies to: $S:$S,$W:$W,$AA:$AA,$AE:$AE,$AI:$AI

The above are the conditions set to each page. If there is a code that I can add to a button I can have the user press the button as they navigate to the sheet to ensure the conditions are always correct and refreshed. Thanks in advance for any help on this.

 

[rlv01]

The main approach with VBA is to

1. Determine the range of cells that do contain, or must contain your format condition
2. Delete all format conditions in that range.
3. Re-establish (i.e. 'refresh') the desired condition in that range.

Sub SetFormatConditionsExample()
    Dim FCRange As Range
    Dim FormulaStr As String, DQ As String
    Dim WS As Worksheet
    
    Set WS = ActiveSheet                    'define worksheet containing format conditions.
    Set FCRange = WS.Range("A1:A20")        'define the range of cells you want to 'refresh'
    
    With FCRange.FormatConditions
        .Delete                             'It is important to clear existing format conditions each time the macro is run
        DQ = """" 'double quote
        FormulaStr = "=$A1=" & DQ & "Promo Ended" & DQ          'Any formula that resolves to True or False
        
        'Set format condition
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = vbGreen
            .Font.Color = RGB(156, 0, 6)
        End With
    End With
End Sub

 

[Johnny Thunder]

This is what I came up with and it works except the last two formats #8-9 that seem to get created but show "No Formats" and they end up overriding the first two formats #1-2 colors to the red and Yellow formats? Any idea on how to correct?

This example is my full list of conditions, my original post included just a few examples to keep the request easier to read.

Sub ConditionalFormattingExample()

'Define Range
Dim MyRange As Range
Dim ws1 As Worksheet

Set ws1 = ActiveSheet
ws1.Cells.FormatConditions.Delete

'1
Set MyRange = ws1.Range("AG:AI")
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$AH1=""Promo Ended"""
        MyRange.FormatConditions(1).Interior.Color = RGB(192, 192, 192)
'2
Set MyRange = ws1.Range("AC:AE")
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$AD1=""Promo Ended"""
        MyRange.FormatConditions(1).Interior.Color = RGB(192, 192, 192)
'3
Set MyRange = ws1.Range("Y:AA")
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$Z1=""Promo Ended"""
        MyRange.FormatConditions(1).Interior.Color = RGB(192, 192, 192)

'4
Set MyRange = ws1.Range("U:W")
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$V1=""Promo Ended"""
        MyRange.FormatConditions(1).Interior.Color = RGB(192, 192, 192)
'5
Set MyRange = ws1.Range("Q:S")
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$R1=""Promo Ended"""
        MyRange.FormatConditions(1).Interior.Color = RGB(192, 192, 192)

'6
Set MyRange = ws1.Range("M:O")
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$N1=""Promo Ended"""
        MyRange.FormatConditions(1).Interior.Color = RGB(192, 192, 192)

'7
Set MyRange = ws1.Range("I:K")
    MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$J1=""Promo Ended"""
        MyRange.FormatConditions(1).Interior.Color = RGB(192, 192, 192)
       
'8 - Yellow Formats
Set MyRange = ws1.Range("$S:$S,$W:$W,$AA:$AA,$AE:$AE,$AI:$AI")
    MyRange.FormatConditions.Add Type:=xlTimePeriod, DateOperator:=xlNextWeek
        MyRange.FormatConditions(1).Interior.Color = RGB(255, 235, 156)
       
'9 -Red Formats
Set MyRange = ws1.Range("$S:$S,$W:$W,$AA:$AA,$AE:$AE,$AI:$AI")
    MyRange.FormatConditions.Add Type:=xlTimePeriod, DateOperator:=xlThisWeek
        MyRange.FormatConditions(2).Interior.Color = RGB(255, 199, 206)
       
End Sub

 

[rlv01]

FormatCondition is an object and it has many properties that control its behavior. If you have more that one formatting rule for the same range of cells you must code such that you decide which rule will apply. Usually this is done with the .StopIfTrue property which tells the code to stop processing formatting rules if the current rule evaluates to True. Note it's use in the example I posted earlier. There is also another property, Priority which can be used in a similar way

FormatCondition object (Excel)

Office VBA reference topic

docs.microsoft.com