Forum Discussion

Radish_G's avatar
Radish_G
Copper Contributor
Oct 15, 2019

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)

 

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

     

    • mirajshah's avatar
      mirajshah
      Copper Contributor

      Subodh_Tiwari_sktneer 


      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.

       

       

       

       

    • MushrMike's avatar
      MushrMike
      Copper Contributor
      4 years later, your response is still saving lives. Thanks for your help.
    • David2860's avatar
      David2860
      Copper Contributor
      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.

Resources