如何使用 VBA 从 Excel 中的条件格式中获取背景颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45122782/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the background color from a Conditional Formatting in Excel using VBA
提问by David Leal
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.Colorproperty 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 技巧来获取此信息。
回答by
If you want to know the color of a cell that has been coloredby a conditional formatting rule (CFR) then use .Range.DisplayFormat.Interior.Color1.
如果您想知道由条件格式规则 (CFR)着色的单元格的颜色，请使用 .Range.DisplayFormat.Interior.Color1。
If you want to definitively know what color a cell may or may not have been coloredby 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 .
1 Note: .DisplayFormat is not available for a worksheet UDF.
1注意：.DisplayFormat 不适用于工作表 UDF。
回答by jainashish
The code below gives the HEXand RGBvalue of the range whether formatted using conditional formattingor 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 DisplayFormatproperty 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 = "#" & 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
回答by Tim Williams
You want Range.DisplayFormatif you need to account for Conditional Formatting
Range.DisplayFormat如果您需要考虑条件格式，您想要
(added in Excel 2010)
（在 Excel 2010 中添加）
回答by Swati Gunwant
.FormatCondition property of range or selection should help with any formatting Use With...End With
范围或选择的 .FormatCondition 属性应该有助于任何格式设置 Use With...End With
If you want to know the exact color RGB values, u can just try recording macro and get the rgb values.
如果您想知道确切的颜色 RGB 值，您可以尝试录制宏并获取 rgb 值。
