So I am adding conditional formatting to a column of cells that is dynamically created by the user with VBA. The problem I am having is that after the first format is added, any subsequent formatting will change the font color of the already formatted cells. There is some conditional formatting in the cells already that is copied from a master source that formats when cells = 0 or "Select One:" to be blue text in a yellow cell Below is the code I have so far:
With Range(Ltrs & 36, Ltrs & 41)
.FormatConditions.Add xlExpression, Formula1:="= $" & Ltrs & "$33 <> ""Custom" & OCV + 1 & """"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.Interior.Color = vbBlack
.Font.Color = vbBlack
.StopIfTrue = False
End With
End With
With Range(Ltrs & 42, Ltrs & 44)
.FormatConditions.Add xlExpression, Formula1:="=AND($" & Ltrs & "$29<>Repack1, $" & Ltrs & "$29<>Repack2)"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.Interior.Color = vbBlack
.Font.Color = vbBlack
.StopIfTrue = False
End With
End With
With Range(Ltrs & 45)
.FormatConditions.Add xlExpression, Formula1:="=AND($" & Ltrs & "$29<>Repack1, $" & Ltrs & "$29<>Repack2)"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.Interior.Color = vbBlack
.Font.Color = vbBlack
.StopIfTrue = False
End With
End With
With Range(Ltrs & 47)
.FormatConditions.Add Type:=xlTextString, String:="Enter", TextOperator:=xlContains
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.Interior.Color = 13421823 'Light Red
.Font.Color = -14614363 'Dark Red/Brown
.StopIfTrue = False
End With
End With
This results in all cells with a 0 to be formatted with red text and all cells with "Select One:" to have black text while cells containing the value "Enter" have blue text. The strange thing is (at least to me) is that the interior cell colors are all still correct, it's just the font color that is wrong.
No comments:
Post a Comment