vba 返回选定单元格的背景颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/520570/
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
Return background color of selected cell
提问by whiz
I have a spreadsheet which cells in are colored meaningfully.
我有一个电子表格，其中的单元格被有意义地着色。
Does any body know how i can return the background color value of a current cell in Excel sheet?
有谁知道我如何在 Excel 工作表中返回当前单元格的背景颜色值？
回答by Garry Shutler
You can use Cell.Interior.Color, I've used it to count the number of cells in a range that have a given background color (ie. matching my legend).
您可以使用Cell.Interior.Color，我已经用它来计算具有给定背景颜色（即匹配我的图例）的范围内的单元格数量。
回答by RPh_Coder
If you are looking at a Table, a Pivot Table, or something with conditional formatting, you can try:
如果您正在查看表格、数据透视表或具有条件格式的内容，您可以尝试：
ActiveCell.DisplayFormat.Interior.Color
This also seems to work just fine on regular cells.
这似乎也适用于常规单元格。
回答by Anton
Maybe you can use this properties:
也许你可以使用这个属性：
ActiveCell.Interior.ColorIndex - one of 56 preset colors
and
和
ActiveCell.Interior.Color - RGB color, used like that:
ActiveCell.Interior.Color = RGB(255,255,255)
回答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.If you are not finding color of the conditionally formatted range, then I encourage you to rather use
然后执行工作表函数以返回 #VALUE！错误。如果您没有找到条件格式范围的颜色，那么我鼓励您宁可使用
Range.Interior.ColorIndex
as then the function can also be used as UDF in Excel. Such as iColor(B1,"HEX")
那么该函数也可以用作 Excel 中的 UDF。如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
