Thursday, 7 April 2016

Stop Font Color From Changing When Adding Conditional Formatting With VBA in Excel 2007

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

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...