vba Excel 2007 条件格式 - 如何获取单元格颜色？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/996384/
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
Excel 2007 conditional formatting - how to get cell color?
提问by
Let's assume i have the following range from (a1:c3)
让我们假设我有以下范围 (a1:c3)
A B C
1 -1 1 1
2 -1 0 0
3 0 0 1
Now i have selected the following range, and formatted it using Conditional Formatting (using default red yellow green color scale).... now range colors became
现在我选择了以下范围，并使用条件格式（使用默认的红黄绿色色标）对其进行了格式化……现在范围颜色变成了
A B C
1 Green Red Red
2 Green Yellow Yellow
3 Yellow Yellow Red
Now I want to ask the color of any cell in the range, for example MsgBox Range("A1").Interior.Color but it does not say that it is Green, why? Plz can you help me?
现在我想询问范围内任何单元格的颜色，例如 MsgBox Range("A1").Interior.Color 但它没有说它是绿色，为什么？lz你能帮帮我吗？
Range("A1").Interior.Color always returns 16777215 Range("A1").Interior.ColorIndex always returns -4142
Range("A1").Interior.Color 总是返回 16777215 Range("A1").Interior.ColorIndex 总是返回 -4142
(no matter whether the color of A1 is red, blue, green, ...)
（不管A1的颜色是红蓝绿……）
Range("A1", "C3").FormatConditions.Count this one returns always 0, why?
Range("A1", "C3").FormatConditions.Count 这个总是返回0，为什么？
采纳答案by SengMing
since i may have more than three different colors in a time... i didn't find any good way of handling this with conditional formatting's default colors... i did it this way. then whenever i ask the color of the cell, i retrieve the correct color!
因为我一次可能有超过三种不同的颜色......我没有找到任何用条件格式的默认颜色处理这个的好方法......我是这样做的。然后每当我询问单元格的颜色时，我都会检索到正确的颜色！
for (int t = 0; t < d_distinct.Length; t++ )
{
Excel.FormatCondition cond =
(Excel.FormatCondition)range.FormatConditions.Add(
Excel.XlFormatConditionType.xlCellValue,
Excel.XlFormatConditionOperator.xlEqual,
"="+d_distinct[t],
mis, mis, mis, mis, mis);
cond.Interior.PatternColorIndex =
Excel.Constants.xlAutomatic;
cond.Interior.TintAndShade = 0;
cond.Interior.Color = ColorTranslator.ToWin32(c[t]);
cond.StopIfTrue = false;
}
d_distinct holds all the distinct values in a range... c is a Color[] which holds distinct colors for every distinct value! this code can easily be translated to vb!
d_distinct 保存了一个范围内的所有不同值... c 是一个 Color[]，它为每个不同的值保存了不同的颜色！这段代码可以很容易地翻译成vb！
回答by richardtallent
.Interior.Color returns the "real" color, not the conditionally-formatted color result.
.Interior.Color 返回“真实”颜色，而不是条件格式的颜色结果。
@sss: It's not available via the API.
@sss：它不能通过 API 获得。
The best you can do is to test the same conditions you used in the conditional formatting.
您能做的最好的事情是测试您在条件格式中使用的相同条件。
To avoid this resulting in duplicate code, I suggest moving your conditional criteria to a UDF. Examples:
为避免这导致重复代码，我建议将您的条件标准移至 UDF。例子：
Function IsGroup1(ByVal testvalue As Variant) As Boolean
IsGroup1 = (testvalue < 0)
End Function
Function IsGroup2(ByVal testvalue As Variant) As Boolean
IsGroup1 = (testvalue = 0)
End Function
Function IsGroup3(ByVal testvalue As Variant) As Boolean
IsGroup1 = (testvalue > 0)
End Function
Then use these formulas in your Conditional formatting:
然后在条件格式中使用这些公式：
=IsGroup1(A1)
=IsGroup2(A1)
=IsGroup3(A1)
Then your code, rather than looking at the colorof the cells, looks to see if the condition is met:
然后您的代码，而不是查看单元格的颜色，查看是否满足条件：
If IsGroup1(Range("$A").Value) Then MsgBox "I'm red!"
回答by Ankita
You need to refer the <Cell>.FormatConditions(index that is active).Interior.ColorIndexto retrieve the conditional formatting color of a cell.
您需要参考<Cell>.FormatConditions(index that is active).Interior.ColorIndex来检索单元格的条件格式颜色。
You may refer to the below link for an example:
您可以参考以下链接作为示例：
http://www.xldynamic.com/source/xld.CFConditions.html#specific
http://www.xldynamic.com/source/xld.CFConditions.html#specific
回答by SengMing
As a follow up to @richardtallent (sorry, I couldn't do comments), the following link will get you a function that returns you the color index by evaluating the conditional formatting for you.
作为@richardtallent 的后续（抱歉，我无法发表评论），以下链接将为您提供一个函数，该函数通过为您评估条件格式来返回颜色索引。
回答by SqlRyan
It doesn't appear that the "Conditional Format"-color is available programmatically. What I'd suggest that, instead, you write a small function that calculates cell color, and then just set a macro to run it on the active cell whenever you've edited the value. For example (sorry for the psuedo-code - I'm not a VBA expert anymore):
“条件格式”颜色似乎无法以编程方式使用。相反，我建议您编写一个计算单元格颜色的小函数，然后只需设置一个宏即可在编辑该值时在活动单元格上运行它。例如（对不起伪代码 - 我不再是 VBA 专家了）：
Function GetColorForThisCell(Optional WhatCell as String) as Int
If WhatCell="" Then WhatCell = ActiveCell
If Range(WhatCell).value = -1 then GetColorForThisCell = vbGreen
If Range(WhatCell).value = 0 then GetColorForThisCell = vbYellow
If Range(WhatCell).value = 1 then GetColorForThisCell = vbRed
End Function
Sub JustEditedCell
ActiveCell.color = GetColorForThisCell()
End Sub
Sub GetColorOfACell(WhatCell as string)
Msgbox(GetColorForThisCell(WhatCell) )
End Sub
Though you wouldn't be able to use the built-in Excel Conditional Formatting, this would accomplish the same thing, and you'd be able to read the color from code. does this make sense?
虽然您将无法使用内置的 Excel 条件格式，但这可以完成同样的事情，并且您可以从代码中读取颜色。这有意义吗？
回答by jitter
According to XlColorIndex EnumerationColorIndex=-4142means No color
根据XlColorIndex 枚举ColorIndex=-4142意味着没有颜色
As to why this happens I'm clueless. The returned value seems to be the decimal representation of the RGB value. The improved version of this scriptto decrypt the value into hex RGB notation
至于为什么会发生这种情况，我一无所知。返回值似乎是 RGB 值的十进制表示。此脚本的改进版本将值解密为十六进制 RGB 表示法
Function RGB(CellRef As Variant)
RGB = ToHex(Range(CellRef).Interior.Color)
End Function
Function ToHex(ByVal N As Long) As String
strH = ""
For i = 1 To 6
d = N Mod 16
strH = Chr(48 + (d Mod 9) + 16 * (d \ 9)) & strH
N = N \ 16
Next i
strH2 = ""
strH2 = Right$(strH, 2) & Mid$(strH, 3, 2) & Left$(strH, 2)
ToHex = strH2
End Function
回答by Andrew Scagnelli
To get the color of a cell in a Range, you need to reference the individual cell inside the array in the form of Range("A1","C3").Cells(1,1) (for cell A1). The Excel help is pretty good if you look up the name of the property you're having issues with.
要获取范围中单元格的颜色，您需要以 Range("A1","C3").Cells(1,1)（对于单元格 A1）的形式引用数组内的单个单元格。如果您查找遇到问题的属性的名称，Excel 帮助非常好。
Also, Excel 2007 uses Integers for its color types, so your best bet is to assign the color index to an integer, and using that throughout your program. For your example, try:
此外，Excel 2007 使用整数作为其颜色类型，因此最好的办法是将颜色索引分配给一个整数，并在整个程序中使用它。对于您的示例，请尝试：
Green = Range("A1","C3").Cells(1,1).Interior.Color
Yellow = Range("A1","C3").Cells(1,3).Interior.Color
Red = Range("A1","C3").Cells(2,1).Interior.Color
And then to switch the colors to all red:
然后将颜色切换为全红色：
Range("A1","C3").Interior.Color = Red
Again, check the Excel help for how to use Cells([RowIndex],[ColumnIndex]).
再次检查 Excel 帮助以了解如何使用 Cells([RowIndex],[ColumnIndex])。
If the above doesn't work for you, check to see what .Interior.PatternColorIndex is equal to. I typically leave it set at xlAutomatic (solid color), and it could be set to something else if the color isn't changing.
如果上述方法对您不起作用，请检查 .Interior.PatternColorIndex 等于什么。我通常将它设置为 xlAutomatic（纯色），如果颜色没有改变，它可以设置为其他内容。
