Friday 29 November 2019

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

php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...