4

I would like to obtain the background color of a cell in an Excel sheet using a UDF formula or VBA. I found this UDF:

Public Function BColor(r As Range) As Long 
    BColor = r(1).Interior.ColorIndex 
End Function

It can be used like this in a cell: =BColor(A1) I'm not familiar with VBA, this returns some long value and I wonder if it is possible to obtain the hex value directly. Thank you!

jainashish
  • 4,702
  • 5
  • 37
  • 48
Radu Bompa
  • 1,594
  • 1
  • 15
  • 21
  • Why the down vote? :-s – Radu Bompa Feb 06 '14 at 08:48
  • 1
    possibly cause it takes less than 10 seconds to find the answer to your question using google –  Feb 06 '14 at 08:54
  • I didn't downvote you but I guess it's for lack of research. Did you search google on how to convert Long Value to Hex in VBA? There are so many examples. – Siddharth Rout Feb 06 '14 at 08:55
  • 2
    Example: http://www.garybeene.com/code/visual%20basic168.htm – Siddharth Rout Feb 06 '14 at 08:55
  • I prefer mehow's routine to the one you found because it makes clear the parameter should be a Cell. If you specify a range that includes two cells with different interior colours, the value returned will be 16777215 (decimal) = "FFFFFF" (Hex) = White. Only if the entire range has the same interior colour will you get the correct value. – Tony Dallimore Feb 06 '14 at 09:22
  • 2
    Warning. The Excel hex value is "BBGGRR". Look at Gary Beene's formula. Everybody else uses "RRGGBB". If you look at the colour numbers in an HTML chart, they will not match the Excel colour numbers. If you are going to set interior colours, use the function RGB(R, G, B) which generates the correct Excel number. – Tony Dallimore Feb 06 '14 at 09:28
  • Indeed, I'll do my homework better next time, but there was no answer to this question on SO, so I hope the question will be useful to someone else. Thank you for the answers! – Radu Bompa Feb 06 '14 at 09:45

3 Answers3

5

try this

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

I tried the above code, but it returned a BGR value (as suggested)

However, this code returns a RGB Hex value. I just can't get it to auto update.

Public Function HexCodeRGB(cell As Range) As String
HexCodeBGR = Right("000000" & Hex(cell.Interior.Color), 6)
HexCodeRGB = Right(HexCodeBGR, 2) & Mid(HexCodeBGR, 3, 2) & Left(HexCodeBGR, 2)
End Function

Good luck

Stu.tech
  • 41
  • 1
0

Based on the best voted answer, here is an example of a real case, my case. I supply it as a macro and a screenshots. I hope this can help someone out.

The VBA macro is simple but I'll paste it here. If you take a look at the attachment you will see that I have duplicated that column and cleared the color names in Spanish and titled the column "color", the I did run this macro:

Sub printHEXBGColor()
Set r = Range("myRange")
Dim HEXcolor As String
Dim i As Long

For i = 1 To r.Rows.Count
  r.Cells(i, 1).Activate
  HEXcolor = "#" + Right("000000" & Hex(ActiveCell.Interior.Color), 6)
  ActiveCell = HEXcolor
Next i
End Sub

Screenshot of the result

Sparker73
  • 303
  • 2
  • 10