CODEWITHSUNDEEP
vbaexcel
Radu Bompa
Radu Bompa

Reputation: 1634

Get Excel cell background color hex value

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!

Upvotes: 7

Views: 37103

Answers (3)

Sparker73
Sparker73

Reputation: 313

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

Upvotes: 0

Stu.tech
Stu.tech

Reputation: 71

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

Upvotes: 7

user2140173
user2140173

Reputation:

try this

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

Upvotes: 5

Related Questions