HEX color codes in VBA

Question

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

Is there a specific reason you need to use Hex over something like RGB? — dwirony, Jul 19 '19 at 17:17
Hexadecimal values aren't "codes", they're numbers, expressed in base 16 (16 symbols) - we're used to seeing numbers in base 10 (10 symbols). You're getting 4 digit values for the same reason we don't express the value `10` as `000010`. — Mathieu Guindon, Jul 19 '19 at 17:19
@dwirony I just need to set a IF sentence so technically RGB works too, but I don't know how to find out RGB code of a cell. — Anctor Hu, Jul 19 '19 at 17:19
@MathieuGuindon So can I understand 'C0FF' is actually '00C0FF'? — Anctor Hu, Jul 19 '19 at 17:20
@AnctorHu exactly, yes; the number of leading zeroes is insignificant — Mathieu Guindon, Jul 19 '19 at 17:20
That said IIRC there *is* something awkward about cell colors expressed in RGB - if I recall, R and B values are inverted. EDIT: [yup](https://stackoverflow.com/q/6003324/1188513) — Mathieu Guindon, Jul 19 '19 at 17:22
All here and more! http://dmcritchie.mvps.org/excel/colors.htm — Tim Williams, Jul 19 '19 at 17:26
@MathieuGuindon Thank you so much. However for the first one 'c0ff', if I search '00c0ff' in https://www.color-hex.com/ the color given out is light blue, whereas the real color in the cell is yellow. How does that happen? — Anctor Hu, Jul 19 '19 at 17:26
R=00, G=C0, B=FF would be a light blue indeed. R=FF, G=C0, B=00 would be some kind of yellow. See previous comment about R and B being reversed. — Mathieu Guindon, Jul 19 '19 at 17:58
Use `Right$(Hex$(&H80000000 Or RGB((Color \ 65536) And 255, (Color \ 256) And 255, Color And 255)), 6)` to convert the color to an RGB hex string, — Florent B., Jul 19 '19 at 18:08

Vityata · Answer 1 · 2019-07-19T18:32:34.820

The Range.Interior.Color property returns RGB:

https://learn.microsoft.com/en-us/office/vba/api/excel.interior.color

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:

score 0 · Answer 2 · answered Mar 29 '23 at 21:29

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.

HEX color codes in VBA

2 Answers2