如何使用 vba 在 Excel 2007 中找到条件格式单元格的填充颜色值? [英] How do I find the fill colour value of a conditionally formatted cell in Excel 2007 using vba?

问题描述

我在 Excel 2007 中为我的条件格式使用色标，我很难找出条件格式单元格的填充颜色代码.我知道 Interior.Color 返回默认颜色值，但这在使用条件格式时无济于事.我对这件事的难度感到非常惊讶.

I am using a colour scale for my conditional formatting in Excel 2007 and I am having a hard time finding out the fill colour code for the conditionally formatted cells. I know Interior.Color returns the default colour value but that does not help when using conditional formatting. I am relay quite surprised at how hard this has been to do.

谢谢.

推荐答案

您可以像这样访问格式化条件(不是单元格当前的颜色)的内部颜色，假设这是应用于单元格的第一个条件:

You can access the interior color of the fomatting conditions (not what the cell currently is) like so, assuming there this is the first condition applied on the cell:

Range("A1").FormatConditions(1).interior.color

这是一个函数，它将返回单元格包含的所有条件格式的颜色代码.如果没有条件，它不会返回任何内容，如果有条件但没有为它设置颜色，那么它会告诉你无".

Here's a function that will return the color codes for all the conditional formats a cell contains. It will return nothing if there are no conditions, and if there is a condition but no color is set for it, then it tells you "none".

Function ConditionalColor(ByVal cell As Range)

Dim colors As String
Dim i As Long

For i = 1 To Range(cell.Address).FormatConditions.count
    If Range(cell.Address).FormatConditions(i).Interior.Color <> 0 Then
        colors = colors & "Condition " & i & ": " & _
        Range(cell.Address).FormatConditions(i).Interior.Color & vbLf
    Else
        colors = colors & "Condition " & i & ": None" & vbLf
    End If
Next

If Len(colors) <> 0 Then
    colors = Left(colors, Len(colors) - 1)
End If

ConditionalColor = colors

End Function

更新:如果您好奇(我曾经)，Excel 使用的颜色代码实际上是 BGR，而不是 RGB.所以如果你想将代码转换为 RGB 值，你可以使用这个:

UPDATE: In case you are curious (I was), the color code that Excel uses is actually BGR, not RGB. So if you wanted to convert the code to RGB values, you can use this:

Function GetRGB(ByVal cell As range) As String

Dim R As String, G As String
Dim B As String, hexColor As String
hexCode = Hex(cell.Interior.Color)

'Note the order excel uses for hex is BGR.
B = Val("&H" & Mid(hexCode, 1, 2))
G = Val("&H" & Mid(hexCode, 3, 2))
R = Val("&H" & Mid(hexCode, 5, 2))

GetRGB = R & ":" & G & ":" & B
End Function

这篇关于如何使用 vba 在 Excel 2007 中找到条件格式单元格的填充颜色值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！