VBA Help - Create Conditional Formatting to Sheet

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
681
Office Version
  1. 2016
Platform
  1. MacOS
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
991
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Apr 9, 2010
Messages
681
Office Version
  1. 2016
Platform
  1. MacOS
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.

VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
991
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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


 

Forum statistics

Threads
1,141,616
Messages
5,707,419
Members
421,509
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top