Conditional Formatting Excel VBA-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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)