I have a macro that does a bunch of conditional formatting {i.e. changes the fort color based on date (items older than today are red, today plus the next 7 days are purple) and shades other items mint green if column G has 1 or 3 or H has a 1 - this probably TMI)}. See below. There's also a sample attached.
The macro works fine. It does exactly what it should. Here's the problem. If I insert or deleted a row, the conditional formatting rule breaks/splits. The best way to describe it is.....the formatting is stuck in/with the cell despite the fact that the data (which is what caused the cell's font/fill to change in the 1st place, but that's the point of a conditional) has been shifted. Any advice on how to fix it?
Sub TicklerConditionals()
'
' TicklerConditionals Macro
'
'
Cells.FormatConditions.Delete
Columns("A:M").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($G1,""3"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("A:M").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($H1,""0"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("A:M").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($G1,""1"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(MEDIAN(TODAY(),$I1,TODAY()-1065)=$I1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(MEDIAN(TODAY()+1,$I1,TODAY()+7)=$I1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -6279056
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Bookmarks