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