如何使用vba在Excel 2007中找到有条件格式化单元格的填充颜色值？

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.

我在Excel 2007中使用颜色比例进行条件格式化，我很难找到条件格式化单元格的填充颜色代码。我知道Interior.Color返回默认颜色值，但在使用条件格式时没有用。我很中庸地意识到这有多么艰难。

Thank you.

谢谢。

5 个解决方案

#1

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:

您可以访问fomatting条件的内部颜色（而不是单元格当前的颜色），假设这是应用于单元格的第一个条件：

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

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:

更新：如果你很好奇（我是），Excel使用的颜色代码实际上是BGR，而不是RGB。因此，如果您想将代码转换为RGB值，可以使用：

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

#2

Hi The answers you have provided did not work because I am using a color scale so it does not return the normal 3 condition values.

嗨您提供的答案无效，因为我使用的是色标，因此它不会返回正常的3个条件值。

After much more searching I found one workaround that works. That is to take the data and put it into word then copy it back into excel making the range go to a true colour in the cell so Interior.Color will work. I found someone that has taken and put it into VBA. Here is the link to it if anyone else is looking to do this.

经过更多搜索后，我找到了一个有效的解决方法。这就是取数据并将其放入word然后将其复制回excel，使得范围在单元格中变为真彩色，因此Interior.Color将起作用。我找到了一个已经把它带入VBA的人。如果其他人想要这样做，这里是它的链接。

#3

I don't have an answer that works with Excel 2007 or lower but from Excel 2010 onwards you can use the following (changing the Range to suit):

我没有适用于Excel 2007或更低版​​本的答案，但从Excel 2010开始，您可以使用以下内容（更改范围以适应）：

Range("A1").DisplayFormat.Interior.ColorIndex

Fortunately, whilst the software for which I need it is supported on Excel 2003 onwards, I only actually require it in a test procedure, and the test module is removed from the production versions.

幸运的是，虽然Excel 2003以后支持我需要它的软件，但实际上我只是在测试过程中需要它，并且测试模块从生产版本中删除。

#4

The code below was taken from VBAExpress, all credit too the original author - byundt.

下面的代码来自VBAExpress，所有信用都是原作者 - byundt。

It may need to be modified for excel 2007.

可能需要针对excel 2007进行修改。

Original Link

原始链接

Function ConditionalColor(rg As Range, FormatType As String) As Long
     'Returns the color index (either font or interior) of the first cell in range rg. If no _
    conditional format conditions apply, Then returns the regular color of the cell. _
    FormatType Is either "Font" Or "Interior"
    Dim cel As Range
    Dim tmp As Variant
    Dim boo As Boolean
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
    Dim i As Long

     'Application.Volatile    'This statement required if Conditional Formatting for rg is determined by the _
    value of other cells

    Set cel = rg.Cells(1, 1)
    Select Case Left(LCase(FormatType), 1)
    Case "f" 'Font color
        ConditionalColor = cel.Font.ColorIndex
    Case Else 'Interior or highlight color
        ConditionalColor = cel.Interior.ColorIndex
    End Select

    If cel.FormatConditions.Count > 0 Then
         'On Error Resume Next
        With cel.FormatConditions
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1
                If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
                     'Conditional Formatting is interpreted relative to the active cell. _
                    This cause the wrong results If the formula isn 't restated relative to the cell containing the _
                    Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
                    If the Function were Not called using a worksheet formula, you could just activate the cell instead.
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
                    boo = Application.Evaluate(frmlaA1)
                Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
                    Select Case .Item(i).Operator
                    Case xlEqual ' = x
                        frmla = cel & "=" & .Item(i).Formula1
                    Case xlNotEqual ' <> x
                        frmla = cel & "<>" & .Item(i).Formula1
                    Case xlBetween 'x <= cel <= y
                        frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
                    Case xlNotBetween 'x > cel or cel > y
                        frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
                    Case xlLess ' < x
                        frmla = cel & "<" & .Item(i).Formula1
                    Case xlLessEqual ' <= x
                        frmla = cel & "<=" & .Item(i).Formula1
                    Case xlGreater ' > x
                        frmla = cel & ">" & .Item(i).Formula1
                    Case xlGreaterEqual ' >= x
                        frmla = cel & ">=" & .Item(i).Formula1
                    End Select
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                End If

                If boo Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    Select Case Left(LCase(FormatType), 1)
                    Case "f" 'Font color
                        tmp = .Item(i).Font.ColorIndex
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex
                    End Select
                    If Err = 0 Then ConditionalColor = tmp
                    Err.Clear
                    On Error GoTo 0
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If
            Next i
        End With
    End If

End Function

#5

Easy way: Print screen the spreadsheet. Paste it into paint. Use the pipet tool to find the color. Click Edit color.

简单方法：打印电子表格屏幕。将其粘贴到油漆中。使用移液工具查找颜色。单击编辑颜色。

BOOM found your RGB information that you can input back into excel

BOOM找到了您可以输入到Excel中的RGB信息