How do I get the corresponding Hex value of an RGB color in Excel/VBA?

Question

I'm trying to set a public const of a color in my VBA code. Normally, I can use:

Dim BLUE As Long
BLUE = RGB(183, 222, 232)

However, there's no way to public const that because of the RGB function. I converted this RGB value to Hex using an online converter, and I got back B7DEE8

Using:

BLUE = &HB7DEE8

results in a completely different color. I think this may actually be an RGBA color, and I've tried B7DEE8__ and got the color pretty close (with the last digit being B8), but I'd like to know how to actually find the correct value.

Note: I don't really need code to convert this to hex, I just need to know how to find it, because I have five constant colors I use on my Excel sheet, and I'd like to set them up.

Answer 1

OK, the following will take the color of a cell in Excel 2010 and provide a valid Hexcode:

Public Function getHexCol(a As Range)

' In excel type in for example getHexCol(A1) to get the hexcode of the color on     A1.
Dim strColour As String
Dim hexColour As String
Dim nColour As Long
Dim nR As Long, nB As Long, nG As Long

strColour = a.Interior.Color
If Len(strColour) = 0 Then Exit Function

nColour = Val(strColour) ' convert string to decimal number
hexColour = Hex(nColour) ' convert decimal number to hex string
While Len(hexColour) < 6 ' pad on left to 6 hex digits
hexColour = "0" & hexColour
Wend

nB = CLng("&H" & Mid(hexColour, 1, 2))
nG = CLng("&H" & Mid(hexColour, 3, 2))
nR = CLng("&H" & Mid(hexColour, 5, 2))

getHexCol = Hex(RGB(nB, nG, nR))
End Function

Answer 2

You'll have to reverse the bytes into order

BLUE = &HE8DEB7

to get the correct color value.