Remove and Reapply Conditional Formatting

[AmandaRen]

Good Afternoon,

I have a worksheet that is conditionally formatted to use 6 formulas. Every time a user pastes in the worksheet, they are messing up the formatting. I understand I can create a VBA to delete and reapply formatting.

I found some formatting that rlv01 (Thank you!) had posted on a similar question. I adjusted the code to fit with what I needed and so far it has worked great. My only issue is that I can only get it to one condition. I had tried changing it to 2 conditions but the last condition would overwrite the first. I need a way to combine both of these VBA's.

Sub Green()
    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("A14:Q200")        '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 = "=AND(NOT(ISBLANK($B14)),AND($B14*$E$8=$O14,$P14=0))" & DQ       'Any formula that resolves to True or False
        
        'Set format condition
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = RGB(198, 239, 206)
            .Font.Color = RGB(0, 0, 0)
        End With
    End With
End Sub

Sub Red()
    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("A14:Q200")        '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 = "=AND($M14<TODAY(),NOT($B14<=0))"  & DQ      'Any formula that resolves to True or False
        
        'Set format condition
        With .Add(Type:=xlExpression, Formula1:=FormulaStr)
            .StopIfTrue = True
            .Interior.Color = RGB(255, 199, 206)
            .Font.Color = RGB(0, 0, 0)
        End With
    End With
End Sub

Thank you!

 

[Jamie McMillan]

Hello Amanda,

If you keep a cell that contains the conditional formatting: below I used cell A1 - you can have any cell

Delete the formatting of a selected range, then copy Cell A1 and paste the formatting to a selected range.

Sub ClearAndFormat()
    Range("B1:O25").FormatConditions.Delete 'Delete the formatting of a selected range

    Application.ScreenUpdating = False
    Range("A1").Copy 'copy Cell A1 that has the conditional formatting
    Range("B1:O25").Select 'select and paste the formatting to a selected range
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Jamie