Conditional Formatting with Macros

luv2learn

New Member
Joined
Jul 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,895
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,895
Office Version
  1. 365
Platform
  1. Windows
The issue is this line right here, which you have in both procedures:
VBA Code:
   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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,895
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,327
Messages
5,547,273
Members
410,783
Latest member
sonnny
Top