如何使用VBA从Excel中的条件格式获取背景颜色

I would like to obtain the cell background color assigned using a conditional formatting rule in Excel in my VBA script. I realized that using Range.Interior.Color property doesn't have the color resulting of an applied conditional formatting feature from Excel.

我想在我的VBA脚本中获取在Excel中使用条件格式设置规则分配的单元格背景颜色。我意识到使用Range.Interior.Color属性不具有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)]

我做了一些研究，我在这里找到了很长的路，它编译并运行，但我没有得到指定的颜色[我得到总是（255,255,255）]

I am using Excel 2016 and I am wondering if there is a simpler way to obtain this information using some built-in VBA function or using any other excel trick.

我正在使用Excel 2016，我想知道是否有更简单的方法来使用一些内置的VBA函数或使用任何其他excel技巧来获取此信息。

4 个解决方案

#1

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¹.

如果您想知道已通过条件格式设置规则（CFR）着色的单元格的颜色，请使用.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.

如果你想明确知道一个单元格可能会或可能没有被CFR着色的颜色，你需要迭代可能影响该单元格的CFR并查看每个.Range.FormatConditions（x）.Interior.Color 。

¹ _{Note: .DisplayFormat is not available for a worksheet UDF.}

¹注意：.DisplayFormat不适用于工作表UDF。

#2

You want Range.DisplayFormat if you need to account for Conditional Formatting

如果需要考虑条件格式，则需要Range.DisplayFormat

(added in Excel 2010)

（在Excel 2010中添加）

#3

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.

下面的代码给出了范围的HEX和RGB值，无论是使用条件格式还是其他方式格式化。如果范围未使用条件格式进行格式化，并且您打算在Excel中将iColor函数用作UDF。它不会起作用。阅读以下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:

请注意，DisplayFormat属性在用户定义的函数中不起作用。例如，在返回单元格内部颜色的工作表函数中，如果使用类似于以下行的行：

Range.DisplayFormat.Interior.ColorIndex

then the worksheet function executes to return a #VALUE! error.

然后工作表函数执行以返回#VALUE！错误。

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 = "#" & Hex(colorVal Mod 256) & Hex((colorVal \ 256) Mod 256) & Hex((colorVal \ 65536))
        Case "RGB"
            iColor = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        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
        myCell.Offset(0, 1).Value = iColor(rng, "HEX")
        myCell.Offset(0, 2).Value = iColor(rng, "RGB")
    Next
End Sub

#4

-3

.FormatCondition property of range or selection should help with any formatting Use With...End With

范围或选择的.FormatCondition属性应该有助于任何格式使用...结束

If you want to know the exact color RGB values, u can just try recording macro and get the rgb values.

如果您想知道RGB值的确切颜色，您可以尝试录制宏并获取rgb值。

#1