Thread: Get Hex code of cell

I have generated a 50 custom color gradient in Excel 2007. Most of these colors are not preset in the color palette. Is there a vba function to get the hex color code for an individual cell filled with a custom color? Thank you in advance for your help.

See if this sample workbook offers you any ideas.

I was able to use that sheet to generate the rgb values separated by commas for the colors. Could somebody help me write a function to convert these numbers to hex. For example, 255, 0, 0 to FF0000.

Hi G.Torrez,

The following macro will tell you the Long, Hex and RGB values for colors in A1:A50. It will put the corresponding values in columns B, C and D, respectively.

Sub getColors()
Dim i As Long
For i = 1 To 50
    Cells(i, 2).Value = Cells(i, 1).Interior.Color
    Cells(i, 3).Value = Hex(Cells(i, 1).Interior.Color)
    With Cells(i, 3)
        Cells(i, 4).Value = _
            WorksheetFunction.Hex2Dec(Right(.Value, 2)) & _
            ", " & WorksheetFunction.Hex2Dec(Mid(.Value, 3, 2)) & _
            ", " & WorksheetFunction.Hex2Dec(Left(.Value, 2))
    End With
Next i
End Sub

Ok, how about this code:

Sub getColors()
Dim i As Long, myColor As Long
For i = 1 To 50
    myColor = Cells(i, 1).Interior.Color
    Cells(i, 2).Value = myColor
    Cells(i, 3).Value = Right("000000" & Hex(myColor), 6)
    Cells(i, 4).Value = Int(myColor Mod 256) & ", " & _
        Int(myColor / 256) Mod 256 & ", " & _
        Int((myColor / 256) / 256) Mod 256
Next i
End Sub

Sub getColors()
Dim i As Long, myColor As Long, hx As String
For i = 1 To 50
    myColor = Cells(i, 1).Interior.Color
    Cells(i, 2).Value = myColor
    hx = Right("000000" & Hex(myColor), 6)
    Cells(i, 3).Value = Right(hx, 2) & Mid(hx, 3, 2) & Left(hx, 2)
    Cells(i, 4).Value = Int(myColor Mod 256) & ", " & _
        Int(myColor / 256) Mod 256 & ", " & _
        Int((myColor / 256) / 256) Mod 256
Next i
End Sub

Should fix that.

Have a look at this workbook.

Run the macro "TestColourCellFromHex" to colour fill Column E

Copy and paste a coloured cell to a cell in Range L2:L6 the formula in Range M2:M6 will return the hex code and the adjacent cell colour will update to suit.

Type a hex number in M10 down the adjacent cell in Column N will change accordingly.

If a scientific number is "detected" precede the hex code with an apostrophe to force a string.
This is not a problem with the function HexCode() it always returns a string.

Hope this helps.

Sorry I missed your posted workbook, it still might be useful to look at the workbook I last posted (Post #11).

I have added this function to your workbook

Function HexCode(Cell As Range) As String
    HexCode = Right("000000" & Hex(Cell.Interior.Color), 6)
End Function

Use as a UDF
In E1

=HexCode(A1)

Drag/Fill Down

Hope this helps.

[EDIT]
None of this will work if the colours are generated by conditional formatting, these colours are only a mask, all of the code you have been given will return the underlying cell colours (the actual cell fill colour).

A handy way to get C/F colours is to copy the range to Word and then back to Excel.
The code for doing this without using Word is horrific, at best