How to get the background color from a Conditional Formatting in Excel using VBA
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.
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
If you want to know the color of a cell that has been colored by a conditional formatting rule (CFR) then use Range.DisplayFormat.Interior.Color
¹.
If you want to definitively know what color a cell may or may not have been colored by a CFR you need to iterate through the CFRs that could be affecting that cell and look at each of the Range.FormatConditions(*x*).Interior.Color
.
Documentation: range.DisplayFormat
¹ Note: .DisplayFormat
is not available for a worksheet UDF.