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

Different color but same color index in Excel VBA

提问by dakiquang

I used the code below to get color index of cell in Excel.

我使用下面的代码来获取 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

But, as illustrated below, cells of 2 different colors give the exact same color index:

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

How to fix this error ?

如何修复此错误？

I attached the test file here. Please check this error.

我在这里附上了测试文件。请检查此错误。

采纳答案by Daniel

Edit: My previous answer does not solve your problem, but I think it may still be relevant to someone asking the same question.

编辑：我之前的回答并没有解决你的问题，但我认为它可能仍然与问同样问题的人有关。

The problem you are seeing stems from the use of the Colorindex property instead of something more specific like Color.

您看到的问题源于使用 Colorindex 属性而不是更具体的东西，如 Color。

For a thorough explanation between the two, you can refer to this address: http://msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx

两者之间的详细解释可以参考这个地址：http: //msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx

Essentially, there are only 57 possible color index values, but far more available colors. The color index refers to the index in a given palette. You happened to stumble upon two colors that have the same index. To have your program function as expected, you should update colorindex references to color. Without making the change you will continue to have confusing results.

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

---

---

Previous answer: If you are using conditional formatting that infers the cell whose value should be applied, then when the UDF checks to determine if the conditional formatting is true it will usually defer to the current cell.

上一个答案：如果您使用条件格式来推断应应用其值的单元格，那么当 UDF 检查以确定条件格式是否为真时，它通常会遵循当前单元格。

For instance, if your conditional formatting formula is something like:

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

=MOD(ROW(),2)=1

=MOD(ROW(),2)=1

Every time the code hits:

每次代码命中：

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

It will evaulate based upon the current active cell instead of the cell for which the conditional formatting is applied.

它将基于当前活动单元格而不是应用条件格式的单元格进行评估。

I did a little experimentation, but depending on how frequently you need to utilize the code I think the best result may be to enhance the formula. This would not solve all the issues, but you could try inserting the following just before the first ConvertFormula call:

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

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

Which solves it for using Row() or Column().

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

If this doesn't completely solve your issue, we'll need to see your conditional formatting formulas.

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

回答by ps-aux

What I think is the very likely cause of the confusion is the conditional formatting. The original ColorIndex or Color of the two to cells is the same. But conditional formatting "overrrides" the original color. Then if you try to get ColorIndex or Color property out of the cell the result is not the one which you see but the original/underlying one.

我认为很可能造成混淆的原因是条件格式。两个 to 单元格的原始 ColorIndex 或 Color 是相同的。但是条件格式“覆盖”了原始颜色。然后，如果您尝试从单元格中获取 ColorIndex 或 Color 属性，则结果不是您看到的那个，而是原始/基础的。

Btw if all you want to get is ColorIndex of the cell you can use something like this code to test if what I described here is your case.

顺便说一句，如果您只想获得单元格的 ColorIndex，您可以使用类似此代码的内容来测试我在此处描述的内容是否符合您的情况。

MsgBox ActiveCell.Interior.ColorIndex

MsgBox ActiveCell.Interior.Color

Or to have it written in the next cell to the right:

或者将其写在右侧的下一个单元格中：

ActiveCell.Offset(0, 1).Value = ActiveCell.Interior.ColorIndex