Forum Discussion
Radish_G
Oct 15, 2019Copper 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)
- Subodh_Tiwari_sktneerSilver Contributor
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")
- mirajshahCopper Contributor
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 valuee.g.
- MushrMikeCopper Contributor4 years later, your response is still saving lives. Thanks for your help.
- alanj_stiCopper ContributorI just had a need today. Thanks
- David2860Copper ContributorI 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.