How to get background color from Conditional Formatting in Excel using VBA
I would like to get the background color of a cell assigned using a conditional formatting rule in Excel in my VBA script. I realized that using the property Range.Interior.Color
has no color as a result of applying conditional formatting from Excel.
I did some research and I found this long way here , it compiles and runs but I don't get the assigned color [I get always (255,255,255)]
I am using Excel 2016 and I am wondering if there is an easier way to get this information with the built-in VBA function or any other excel trick.
source to share
If you want to know the color of a cell that has been colored with a Conditional Formatting Rule (CFR), use .Range.DisplayFormat.Interior.Color¹.
If you want to definitively know what color of a cell may or may not be colored by CFR, you need to iterate through the CFRs that can affect that cell and look at each one. Range.FormatConditions (x) .Interior.Color.
¹ Note: .DisplayFormat is not available for UDF sheet.
The code below gives the HEX and RGB values of a range formatted using conditional formatting or otherwise. Unless the range is formatted using conditional formatting and you intend to use the iColor function in Excel as a UDF. It won't work. Read the excerpt from MSDN below .
Note that the DisplayFormat property does not work in custom functions. For example, in a worksheet function that returns the inner color of a cell, if you use a string similar to the following:
Range.DisplayFormat.Interior.ColorIndex
then the worksheet function is executed, returning #VALUE! mistake.
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
source to share