# Formula to find the cell color value (RGB & Color Index Value)

Copper Contributor

# Formula to find the cell color value (RGB & Color Index Value)

Hi Community,

Is there any formula to find the cell colour value (RGB & Color Index Value) in Excel?

Following image shows my requirement.

Sample for Color Index Value (AutoCAD)

6 Replies

# Re: Formula to find the cell color value (RGB & Color Index Value)

You may use the following User Defined Function to get the Color Index or RGB value of the cell color.

Place the following function on a Standard Module like Module1...

``````Function getColor(Rng As Range, ByVal ColorFormat As String) As Variant
Dim ColorValue As Variant
ColorValue = Cells(Rng.Row, Rng.Column).Interior.Color
Select Case LCase(ColorFormat)
Case "index"
getColor = Rng.Interior.ColorIndex
Case "rgb"
getColor = (ColorValue Mod 256) & ", " & ((ColorValue \ 256) Mod 256) & ", " & (ColorValue \ 65536)
Case Else
getColor = "Only use 'Index' or 'RGB' as second argument!"
End Select
End Function``````

And then assuming you want to check the color index or the RGB of the cell A2, try the UDF on the worksheet like below...

To get Color Index:

``=getcolor(A2,"index")``

To get RGB:

``=getcolor(A2,"rgb")``

# Re: Formula to find the cell color value (RGB & Color Index Value)

Wow, it works well. Thank you so much for your support.

But, after trying out this UDF. I got a few questions popped up in my mind.

1. Noticed the duplicate colour index number.

2. The index & RGB value is not updated instantly after I change the cell colour.

3. And Is it possible to colour fill a cell using the RGB value?

# Re: Formula to find the cell color value (RGB & Color Index Value)

I like this, thanks, but I'm inherently lazy and tried to simplify it so I wouldn't need to type in the extra argument. My rewrite to only use the 'index' version "Function getColor2(Rng As Range) As Variant | getColor2 = Rng.Interior.ColorIndex | End Function" got an unexpected result. Cells without highlighting returned a value of -4142 for each formula version, but a cell with a yellow background (RGB 255,255,0) returned 6 with your original formula but 27 with my abridged version. Would you know why this would happen? Thanks.

# Re: Formula to find the cell color value (RGB & Color Index Value)

4 years later, your response is still saving lives. Thanks for your help.

# Re: Formula to find the cell color value (RGB & Color Index Value)

I just had a need today. Thanks

# Re: Formula to find the cell color value (RGB & Color Index Value)

It really worked well,
although when I changed the color of the cell, the function is not updating the values in real time
I have to go to the cell, press F2 , followed by Enter than only it refreshes to reflect correct updated value

e.g.