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

Question

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.

That would make me assume the opposite. Does it indicate at least SP2 in the version information? — Rory

TinyTim TinyTim · Accepted Answer · 2014-06-04T15:05:58

Try something like this where start is the character you want to start conditional formatting on and Length is the number of characters you need formatted.

Range("B2", "H11").Characters(Start, Length).Font.Size = 14

Range("B2", "H11").Characters(Start, Length).Font.Bold = True

Range("B2", "H11").Characters(Start, Length).Font.Underline = True

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

1 Answers