vba - Excel VBA中颜色不同但颜色索引相同

Question

我使用下面的代码在 Excel 中获取单元格的颜色索引。

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

但是，如下图所示，2 种不同颜色的单元格给出完全相同的颜色索引:

如何解决这个错误？

我附上了测试文件here .请检查此错误。

Answer 1

编辑:我之前的回答没有解决您的问题，但我认为它可能仍然与提出相同问题的人相关。

您看到的问题源于使用了 Colorindex 属性，而不是像 Color 这样更具体的属性。

关于两者的详尽解释，可以引用这个地址: http://msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx

基本上，只有 57 种可能的颜色索引值，但可用的颜色要多得多。颜色索引是指给定调色板中的索引。你偶然发现了两种具有相同索引的颜色。为了让你的程序按预期运行，你应该更新 colorindex 对颜色的引用。如果不进行更改，您将继续得到令人困惑的结果。

---

以前的回答:如果您使用的是推断应应用其值的单元格的条件格式，那么当 UDF 检查以确定条件格式是否为真时，它通常会推迟到当前单元格。

例如，如果您的条件格式公式类似于:

=MOD(ROW(),2)=1

每次代码命中:

frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)                      
boo = Application.Evaluate(frmlaA1) 

它将根据当前事件单元格而不是应用了条件格式的单元格进行计算。

我做了一些实验，但根据您需要使用代码的频率，我认为最好的结果可能是增强公式。这不会解决所有问题，但您可以尝试在第一次 ConvertFormula 调用之前插入以下内容:

frmla = Replace(frmla, "()", "(" & cel.Address & ")")

这解决了使用 Row() 或 Column() 的问题。

如果这不能完全解决您的问题，我们将需要查看您的条件格式公式。