Conditional Formatting Excel VBA

Question

Hi, every day i have to update an excel file. This includes formatting column B. (see picture above). I haven't found VBA code yet, to geht this kind of formatting via VBA. in the picture you see a subset of formatting rules, there are more. But there is only those three colors, which I have the hex code.

yellow #9C5700

red #9C0006

green #006100

' (1) Highlight defined good as green values
With Range("b:b").FormatConditions.Add(xlCellValue, xlEqual, "=2")
    .Interior.ColorIndex = 6
    .StopIfTrue = False
End With

With Range("b:b").FormatConditions.Add(xlCellValue, xlEqual, "in Anfrage")
    .Interior.ColorIndex = 6
    .StopIfTrue = False
End With



' (2) Highlight defined ok as yellow values
With Range("b:b").FormatConditions.Add(xlCellValue, xlEqual, "=1")
    .Interior.ColorIndex = 4
    .StopIfTrue = False
End With

With Range("b:b").FormatConditions.Add(xlCellValue, xlEqual, "ok")
    .Interior.ColorIndex = 4
    .StopIfTrue = False
End With


' (2) Highlight defined bad as red values
With Range("b:b").FormatConditions.Add(xlCellValue, xlEqual, "=3")
    .Interior.ColorIndex = 3
    .StopIfTrue = False
End With

With Range("b:b").FormatConditions.Add(xlCellValue, xlEqual, "kritisch")
    .Interior.ColorIndex = 3
    .StopIfTrue = False
End With

End Sub

I used this code, but i would like to use the hex colors. How do I use those?

Should I understand that you only have a problem with applying of HEX code colors? I prepared my answer before you updated the question posting your code... — FaneDuru
– FaneDuru, Commented Sep 8, 2022 at 8:06

Spencer Barnes · Accepted Answer · 2022-09-08 08:00:14Z

1

Per this Article:

You can assign the color codes to any Color property of any object in either their decimal or hex representation. Precede the Hex value with the &H prefix

However for some reason VBA does swap the first two characters with the last two characters of a hex code, so your Yellow 9C7500 would go into VBA as 00759C

So, instead of .Interior.ColorIndex, use .Interior.Color and put in your hex codes with &H at the start.
Example:

' (1) Highlight defined good as green values
With Range("b:b").FormatConditions.Add(xlCellValue, xlEqual, "=2")
    .Interior.Color = &H006100
    .StopIfTrue = False
End With

answered Sep 8, 2022 at 8:00

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

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

Comments

tnavidi · Accepted Answer · 2022-09-08 08:03:50Z

1

You can use .Color instead of .ColorIndex and you can also use RGB() to more easily set the value so change your code to

.Interior.Color = RGB(&H9C,&H57,&H00)

answered Sep 8, 2022 at 8:03

tnavidi

1,4181 gold badge8 silver badges16 bronze badges

Comments

FaneDuru · Accepted Answer · 2022-09-08 08:00:33Z

Please, try the next code. Formatting the whole column will consume a lot of Excel resources, slows down the process of formulas update and it useless. The above code format only the B:B column having data:

Sub SetFormatRngMultiple_Cond()
 Dim ws As Worksheet, lastR As Long, rngF As Range

 Set ws = ActiveSheet
 lastR = ws.Range("B" & ws.rows.count).End(xlUp).row
 Set rngF = ws.Range("B2:B" & lastR)
 With rngF
        'first condition:
        With .FormatConditions
                .Delete
                .Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=2"
        End With
        With .FormatConditions(.FormatConditions.count)
                .Font.Color = 1137094
                .Interior.Color = vbYellow
                .SetFirstPriority: .StopIfTrue = False
        End With
        
        'second condition:
         With .FormatConditions
                .Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=1"
        End With
        With .FormatConditions(.FormatConditions.count)
                .Font.Color = 5287936
                .Interior.Color = 11854022
                .StopIfTrue = False
        End With
        
        'third condition:
         With .FormatConditions
                .Add Type:=xlTextString, String:="OK", TextOperator:=xlContains
        End With
        With .FormatConditions(.FormatConditions.count)
                .Font.Color = 5287936
                .Interior.Color = 11854022
                .StopIfTrue = False
        End With
        
        'fourth condition:
         With .FormatConditions
                .Add Type:=xlTextString, String:="kritish", TextOperator:=xlContains
        End With
        With .FormatConditions(.FormatConditions.count)
                .Font.Color = vbRed
                .Interior.Color = 14083324
                .StopIfTrue = False
        End With
 End With
End Sub

Collectives™ on Stack Overflow

Conditional Formatting Excel VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related