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
– Rory, Commented Jun 4, 2014 at 16:52

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 4, 2014 at 15:05

TinyTim

762 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

110SidedHexagon Over a year ago

The problem with that is that the conditional formatting will overwrite whatever I set it to. The conditional formatting itself is actually changing from what I am trying to st it to, not just the cell formatting.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related