34

I have a spreadsheet which cells in are colored meaningfully.

Does any body know how i can return the background color value of a current cell in Excel sheet?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
whiz
  • 1,063
  • 2
  • 11
  • 19

5 Answers5

41

You can use Cell.Interior.Color, I've used it to count the number of cells in a range that have a given background color (ie. matching my legend).

Garry Shutler
  • 32,260
  • 12
  • 84
  • 119
24

If you are looking at a Table, a Pivot Table, or something with conditional formatting, you can try:

ActiveCell.DisplayFormat.Interior.Color

This also seems to work just fine on regular cells.

RPh_Coder
  • 833
  • 8
  • 15
  • Finally this is a correct answer. The answers so far are wrong. Cell.Interior.Color always returns the same value, no matter what I have set it to (I tested with Office 2010 and some older versions). Excel has extremely wired behavior here. – Patrick Fromberg Jul 23 '14 at 13:38
  • I agree that this solution is the only one that works consistently to return, not set, the background color, as per the original question. – Rich Harding Jan 16 '15 at 13:14
12

Maybe you can use this properties:

ActiveCell.Interior.ColorIndex - one of 56 preset colors

and

ActiveCell.Interior.Color - RGB color, used like that:

ActiveCell.Interior.Color = RGB(255,255,255)
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Anton
  • 3,006
  • 3
  • 26
  • 37
8

The code below gives the HEX and RGB value of the range whether formatted using conditional formatting or otherwise. If the range is not formatted using Conditional Formatting and you intend to use iColor function in the Excel as UDF. It won't work. Read the below excerpt from MSDN.

Note that the DisplayFormat property does not work in user defined functions. For example, in a worksheet function that returns the interior color of a cell, if you use a line similar to:

Range.DisplayFormat.Interior.ColorIndex

then the worksheet function executes to return a #VALUE! error. If you are not finding color of the conditionally formatted range, then I encourage you to rather use 

Range.Interior.ColorIndex

as then the function can also be used as UDF in Excel. Such as iColor(B1,"HEX")

Public Function iColor(rng As Range, Optional formatType As String) As Variant
'formatType: Hex for #RRGGBB, RGB for (R, G, B) and IDX for VBA Color Index
    Dim colorVal As Variant
    colorVal = rng.DisplayFormat.Interior.Color
    Select Case UCase(formatType)
        Case "HEX"
            iColor = "#" & Format(Hex(colorVal Mod 256),"00") & _
                           Format(Hex((colorVal \ 256) Mod 256),"00") & _
                           Format(Hex((colorVal \ 65536)),"00")
        Case "RGB"
            iColor = Format((colorVal Mod 256),"00") & ", " & _
                     Format(((colorVal \ 256) Mod 256),"00") & ", " & _
                     Format((colorVal \ 65536),"00")
        Case "IDX"
            iColor = rng.Interior.ColorIndex
        Case Else
            iColor = colorVal
    End Select
End Function

'Example use of the iColor function
Sub Get_Color_Format()
    Dim rng As Range

    For Each rng In Selection.Cells
        rng.Offset(0, 1).Value = iColor(rng, "HEX")
        rng.Offset(0, 2).Value = iColor(rng, "RGB")
    Next
End Sub
jainashish
  • 4,702
  • 5
  • 37
  • 48
  • I created a Module with your code, and in a cell typed "=icolor(A2, "HEX")", where A2 has a colored cell. I get an error in the Sub's For loop. I'm new to VBA. Can you please advise how to get this to work? – Lizz Jun 09 '18 at 11:28
  • You get error while using user defined function iColor() or while running the sub-routine? – jainashish Jun 09 '18 at 11:55
  • It's an error in the cell while using the function. It simply says "#VALUE!", even if I just enter "=icolor(A2)". – Lizz Jun 09 '18 at 12:55
  • 1
    To avoid #Value!, please use rng.Interior.ColorIndex instead of rng.DisplayFormat.Interior.Color. – jainashish Jun 09 '18 at 13:08
  • That got rid of the error - thanks! - but now the color is coming back the same for any cell. In a column of data that's been highlighted with Conditional Formatting, all cells give "-4142" as the value - whether or not they they met the condition to format as red. – Lizz Jun 09 '18 at 13:38
  • 1
    To get the colour of conditional formatting, you have to use rng.DisplayFormat.Interior.ColorIndex which is not allowed in UDF. So, it's better to run the sub-routine instead of using UDF as given in the answer. – jainashish Jun 09 '18 at 14:04
  • Thanks - that's great info I haven't seen anywhere else. I'm searching for how to run the sub by itself. Seems obvious but I keep running into walls. – Lizz Jun 09 '18 at 14:46
0

I had an issue matching the cell color with the actual color of the presets despite using the same index from ActiveCell.Interior.ColorIndex. Instead, I followed @jainashish answer above and applied each RGB value into the VBA function.

Dim colorVal As Variant
colorVal = tempFile.Worksheets("Dashboard").Range("F2").DisplayFormat.Interior.color

iColor = Format((colorVal Mod 256), "00") & ", " & _
    Format(((colorVal \ 256) Mod 256), "00") & ", " & _
    Format((colorVal \ 65536), "00")
iColorSplitRGB = Split(iColor, ",")
r = iColorSplitRGB(0)
g = iColorSplitRGB(1)
b = iColorSplitRGB(2)

tempFile.Worksheets("Dashboard").Range("B8").Interior.color = RGB(r, g, b)
William Humphries
  • 541
  • 1
  • 10
  • 21