Excel VBA中颜色不同但颜色索引相同 [英] Different color but same color index in Excel VBA
问题描述
我使用下面的代码来获取Excel中单元格的颜色索引.
I used the code below to get color index of cell in Excel.
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:
如何解决此错误?
我在此处附加了测试文件.请检查此错误.
I attached the test file here. Please check this error.
推荐答案
我之前的答案不能解决您的问题，但我认为它可能仍然与提出相同问题的人有关.
My previous answer does not solve your problem, but I think it may still be relevant to someone asking the same question.
您看到的问题源于使用Colorindex属性，而不是使用诸如Color之类的更具体的内容.
The problem you are seeing stems from the use of the Colorindex property instead of something more specific like Color.
要详细了解两者之间的关系，可以参考以下地址: http://msdn.microsoft.com/en -us/library/cc296089(v = office.12).aspx
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
基本上，只有57个可能的颜色索引值，但可用的颜色更多.颜色索引是指给定调色板中的索引.您偶然发现了具有相同索引的两种颜色.为了使程序正常运行，应将colorindex引用更新为color.如果不进行更改，您将继续产生令人困惑的结果.
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.
上一个答案: 如果您使用条件格式来推断应应用其值的单元格，则当UDF检查以确定条件格式是否为true时，通常将其推迟到当前单元格.
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.
例如，如果您的条件格式公式如下:
For instance, if your conditional formatting formula is something like:
= MOD(ROW()，2)= 1
=MOD(ROW(),2)=1
每次代码被点击时:
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.
我做了一些实验，但是根据您需要多长时间使用一次代码，我认为最好的结果可能是增强公式. 这不能解决所有问题，但是您可以尝试在第一个ConvertFormula调用之前插入以下内容:
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:
frmla = Replace(frmla, "()", "(" & cel.Address & ")")
解决了使用Row()或Column()的问题.
Which solves it for using Row() or Column().
如果这不能完全解决您的问题，我们将需要查看您的条件格式公式.
If this doesn't completely solve your issue, we'll need to see your conditional formatting formulas.
这篇关于Excel VBA中颜色不同但颜色索引相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
