Conditional Formatting with Macros

[luv2learn]

I wrote a macro which applies conditional formatting to a column. Then I wrote another macro which apples conditional formatting to the column next to it. When I run the second macro the conditional formatting from the previous column is gone. Can I fix this.

 

[Joe4]

Welcome to the Board!

Can you post the code from your two Macros?
Please be sure to use the "Code tags" button to post your code, so it is posted in a reader-friendly manner. It is the icon that has "vba" in it.

 

[luv2learn]

Sub t_1()
'
' t_1 Macro
'

'
    Range("H3").Select
    Application.CutCopyMode = False
    Cells.FormatConditions.Delete
    Range("H3:H400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($H2-$H3)/$H3)*100>50"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("H3:H400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($H2-$H3)/$H3)*100>75"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("H3:H400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($H2-$H3)/$H3)*100>90"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub
Sub t_1_2()
'
' t_1_2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Application.CutCopyMode = False
    Cells.FormatConditions.Delete
    Range("H3:H400").Select
    Range("I3").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($H2-$H3)/$H3)*100>50"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = 0
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399914548173467
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("H3:H400").Select
    Range("I3").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($H2-$H3)/$H3)*100>75"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = 0
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.399914548173467
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("H3:H400").Select
    Range("I3").Activate
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($H2-$H3)/$H3)*100>90"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = 0
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399914548173467
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("I3:I400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($I2-$I3)/$I3)*100>30"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("I3:I400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($I2-$I3)/$I3)*100>50"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("I3:I400").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=(($I2-$I3)/$I3)*100>75"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399945066682943
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Windows("PERSONAL.XLSB").Activate
    Windows("_Reduced_Merged-InstStraddleDF_2020-07-05-21-16-03.xlsx").Activate
End Sub

 

[Joe4]

The issue is this line right here, which you have in both procedures:

   Cells.FormatConditions.Delete

That removes ALL prior Conditional Formatting from the entire sheet.
So, whenever you run one of those procedures, it first deletes all existing Conditional Formatting.

You will need to remove that line from one or both of your procedures.
If you always run the second procedure after the first one, then you want to get rid of iit n the second procedurs.
If you may run them in any order, then you will want to remove them from both procedures.

Note that you can also combine these two procedures into one single procedure, and then you don't need to worry about the order.

 

[luv2learn]

Thank you Joe4 that worked great!

 

[Joe4]

You are welcome.