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

New Contributor

Hi Community,

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

Following image shows my requirement.

EC.png

 

 

 

Sample for Color Index Value (AutoCAD)

AC.png

 

3 Replies

@Radish_G 

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")

 

Hi @Subodh_Tiwari_sktneer,

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?

Colour.png

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.