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

Question

我在Excel 2007中使用条件格式的颜色比例，我很难找到条件格式单元格的填充颜色代码。我懂内饰。Color返回默认颜色值，但这在使用条件格式时没有帮助。我真的很惊讶做到这一点有多么困难。

谢谢。

Answer 1

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

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

下面是一个函数，它将返回单元格包含的所有条件格式的颜色代码。如果没有条件，它将不返回任何内容，如果有条件但没有为其设置颜色，则它会告诉您"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值，你可以使用这个:

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

Answer 2

嗨，你提供的答案没有工作，因为我使用的是色标，所以它不返回正常的3个条件值。

经过更多的搜索，我找到了一个工作的解决方案。这是采取的数据，并把它放入word，然后复制回excel使范围去一个真正的颜色在单元格内部。颜色可以。我找到了一个已经采取并把它放进VBA的人。如果有人想这样做，这里是它的链接。

Answer 3

我没有一个适用于Excel 2007或更低版本的答案，但从Excel 2010起，您可以使用以下方法(更改范围以适应):

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

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

Answer 4

下面的代码来自VBAExpress，所有的功劳都归功于原作者- byundt。

对于excel 2007可能需要修改

原始链接

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

Answer 5

简单的方法:打印屏幕电子表格。把它粘到油漆里。使用移液管工具找到颜色。点击"编辑颜色"

BOOM找到你的RGB信息，你可以输入回excel