Is there really no built-in worksheet function to convert RGB values to their hexadecimal equivalent?

[JenniferMurphy]

I cannot find a worksheet function to convert RGB values to their hexadecimal equivalents. Is that correct or did I miss something?

So I wrote this little UDF:

Function RGB2Hex(R As Long, G As Long, B As Long) As Variant

RGB2Hex = Application.WorksheetFunction.Dec2Hex(R) _
        & Application.WorksheetFunction.Dec2Hex(G) _
        & Application.WorksheetFunction.Dec2Hex(B)
       
End Function

Is there something better?

 

[Rick Rothstein]

You can always use VB's built-in Hex function to simplify your function. If you are absolutely sure you want the hex values concatenated as you show, your function can be simplified to this...

Function RGB2Hex(ByVal R As Long, ByVal G As Long, ByVal B As Long) As Variant
  RGB2Hex = Hex(R) & Hex(G) & Hex(B)
End Function

Note I added "ByVal" in front of each of your function arguments... this avoids your function erroring out under certain conditions.

Now, you may be wondering why I wrote "If you are absolutely sure you want the hex values concatenated as you show". It has been a long while since I had to play with color values, but my recollection is that color hex values are concatenated in reverse order (B, G, R) . If I am correct (perhaps someone more knowledgeable about this will come along and clarify it for us), then this function, which does that, may be what you actually need...

Function RGB2Hex(ByVal R As Long, ByVal G As Long, ByVal B As Long) As Variant
  RGBtoHEX = Hex(RGB(R, G, B))
End Function

 

[MARK858]

Maybe

Function HexFromRGB(Red As Long, Green As Long, Blue As Long) As String

HexFromRGB = "#" & Right("00" & Hex(Red), 2) & _
    Right("00" & Hex(Green), 2) & Right("00" & Hex(Blue), 2)

End Function

Should give FFD700 for Red = 255, Green = 215, Blue = 0, I think the functions previously give FFD70.

or formula wise

Book1
	A	B	C
1	255	215	0
2
3
4	#FFD700
Sheet9

Cell Formulas
Range	Formula
A4	A4	="#" & DEC2HEX((A1*65536)+(B1*256)+C1,6)

 

[JenniferMurphy]

How about this inline code for when R, G, & B are in E6, F6, & G6?

="#"&TEXT(DEC2HEX(E6),"00") & TEXT(DEC2HEX(F6),"00") & TEXT(DEC2HEX(G6),"00")

It seems to work. It gives the correct result in Mark's example above.

 

[MARK858]

It seems to work

Seems to work so far for me with light testing ? You can compare your results with the converter at the link below...

RGB to Hex color converter

RGB to hex color code conversion.

www.rapidtables.com

 

[Rick Rothstein]

How about this inline code for when R, G, & B are in E6, F6, & G6?

="#"&TEXT(DEC2HEX(E6),"00") & TEXT(DEC2HEX(F6),"00") & TEXT(DEC2HEX(G6),"00")

If that is really the order you actually want the result in (if you haven't seen it yet, see Message #2), then this simpler formula should do the same thing...

="#"&DEC2HEX(65536*E6+256*F6+G6)