CODEWITHSUNDEEP
excelvbahex
Anctor Hu
Anctor Hu

Reputation: 41

HEX color codes in VBA

Hi I am using HEX() to indicate the color of specific cells in excel. I did 4 examples and results given are confusing. 2 of 4 gave 6-digits HEX code and the other 2 gave 4-digits HEX code. I did a search online and seems like HEX code has to be 6-digits? So what do these 4-digits code stand for?

I also put these 4-digits code in https://www.color-hex.com/ and no results were found.

Sub showcolor()

Debug.Print Hex(ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(10, "AB").Interior.Color)
Debug.Print Hex(ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(34, "AB").Interior.Color)
Debug.Print Hex(ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(13, "AB").Interior.Color)
Debug.Print Hex(ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Cells(12, "AB").Interior.Color)

End Sub

The results given are

C0FF FFFF F0B000 FFFFFF

Upvotes: 4

Views: 35451

Answers (2)

ViKiNG
ViKiNG

Reputation: 1334

If you are reading or saving the color hex code in a string i.e. 00FF00 for green, The following VBA code segment should translate the hex color value to the VBA color value:

//Variables
Dim ColorName as String
Dim ColorValue as long

//Translation
ColorName='00FF00'
ColorValue=Clng("&H" & ColorName)

//Use in the Code
Me.TextBox1.BorderColor=ColorValue

Should work.

Upvotes: 0

Vityata
Vityata

Reputation: 43575

The Range.Interior.Color property returns RGB:

Thus, in the FFFF, the first two values of the Hex() are skipped. The correct one should be 00FFFF. This is achievable with a concatenation trick:

Right("000000" & Hex(Worksheets(1).Cells(1, "A").Interior.Color), 6)

Furthermore, as mentioned in the comments by @Mathiew Guindon, the RGB values in VBA are reversed (for whatever reason). Then a nice reverse back of these two is needed. This is done in the HexToRgb() function:

Sub TestMe()

    Worksheets(1).Cells(1, "A").Interior.Color = vbYellow
    Debug.Print Hex(Worksheets(1).Cells(1, "A").Interior.Color)  'FFFF
    Debug.Print Worksheets(1).Cells(1, "A").Interior.Color       '65535

    Dim hexColor As String
    hexColor = Right("000000" & Hex(Worksheets(1).Cells(1, "A").Interior.Color), 6)

    Debug.Print HexToRgb(hexColor)                               'FFFF00

End Sub

Public Function HexToRgb(hexColor As String) As String

    Dim red As String
    Dim green As String
    Dim blue As String

    red = Left(hexColor, 2)
    green = Mid(hexColor, 3, 2)
    blue = Right(hexColor, 2)

    HexToRgb = blue & green & red

End Function

And ffff00 is yellow - https://www.color-hex.com/color/ffff00:

enter image description here

Upvotes: 6

Related Questions