Determine Cell background color

[Ormus]

I am trying to determine what the background color is of a cell. What I really need is if is cell backround color is not blank. So if A1 background is blank/none, B1 = 0 and if A2 has a yellow background, B2 = 1.

Thanks

 

[Andrew Poulsom]

How is the cell being coloured? If it's by conditional formatting what are the conditions?

 

[Ormus]

Cell background color is done manually

 

[Andrew Poulsom]

There's a custom function ColorIndexOfOneCell that you can use here:

http://www.cpearson.com/Excel/colors.aspx

 

[Ormus]

Will take a look at it, thanks

 

[Sektor]

Function CellColor() As String
    CellColor = GetRGB(ActiveCell.Interior.Color)
End Function

Function GetRGB(RGB As Long) As String

   Dim HexString As String
   Dim r As Double, g As Double, b As Double

   HexString = Hex(RGB)
   HexString = Right(String$(5, "0") & HexString, 6)

   r = CDbl("&H" & Mid$(HexString, 5, 2))
   g = CDbl("&H" & Mid$(HexString, 3, 2))
   b = CDbl("&H" & Mid$(HexString, 1, 2))
   
   GetRGB = "RGB(" & r & ", " & g & ", " & b & ")"

End Function

 

[Ormus]

I will check that as well, thanks

 

[Ormus]

Question with this code...

Function CellColor() As String
    CellColor = GetRGB(ActiveCell.Interior.Color)
End Function

Function GetRGB(RGB As Long) As String

   Dim HexString As String
   Dim r As Double, g As Double, b As Double

   HexString = Hex(RGB)
   HexString = Right(String$(5, "0") & HexString, 6)

   r = CDbl("&H" & Mid$(HexString, 5, 2))
   g = CDbl("&H" & Mid$(HexString, 3, 2))
   b = CDbl("&H" & Mid$(HexString, 1, 2))
   
   GetRGB = "RGB(" & r & ", " & g & ", " & b & ")"

End Function

I placed that in module1. Doing =GetRBG(A1) results in RBG(0, 0, 0). Does not matter what color I have A1 set as a background, does not change. I am doing this correctly?

 

[Andrew Poulsom]

Try this:

Function CellColor(Cell As Range) As String
    CellColor = Cell.Interior.ColorIndex
End Function

and:

=CellColor(A1)

 

[Ormus]

There's a custom function ColorIndexOfOneCell that you can use here:

http://www.cpearson.com/Excel/colors.aspx

I was able to get a UDF from here and get it working, thanks again!