用于测试单元格是否在 Excel 中进行条件格式设置的 VBA 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22366145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA function to test if cell is conditionally formatted in Excel
提问by Chris
I have written the function below to test if a cell has conditional formatting activated based upon the cell fill.
我编写了下面的函数来测试单元格是否根据单元格填充激活了条件格式。
Function cfTest(inputCell)
If inputCell.DisplayFormat.Interior.Color <> 16777215 Then
cfTest = True
Else
cfTest = False
End If
End Function
It does not work however. Saying that, this method does.
但是它不起作用。话虽如此，这个方法确实如此。
Sub myCFtest()
Dim R As Integer
R = 2
Do
If Range("I" & R).DisplayFormat.Interior.Color <> 16777215 Then
Range("K" & R).Value = True
Else
Range("K" & R).Value = False
End If
R = R + 1
Loop Until R = 20
End Sub
Can anyone explain to me why the function will not work?
谁能向我解释为什么该功能不起作用？
Cheers.
干杯。
EDIT: Updated function but not working for conditional formatting
编辑：更新功能但不适用于条件格式
Function cfTest(inputCell)
If inputCell.Interior.ColorIndex <> -4142 Then
cfTest = True
Else
cfTest = False
End If
End Function
回答by Chris
Here is a working demo if the desired result. Column E looks at column D and displays the value TRUE if it is conditionally formatted by cell fill color. i.e. click on the name 'Bob', and conditionally formatting highlights the cell via the code below
如果需要结果，这里是一个工作演示。E 列查看 D 列，如果它按单元格填充颜色有条件地格式化，则显示值 TRUE。即单击名称“鲍勃”，并通过以下代码有条件地格式化突出显示单元格
=IF(AND(CELL("row")=ROW(D1),CELL("col")=COLUMN(D1)),TRUE)
Click on another name, and the same result occurs.
单击另一个名称，会出现相同的结果。
However, when I click off the names onto another cell, I last name selected remains highlighted, giving the impression of a button still depressed.
但是，当我在另一个单元格上单击名称时，我选择的姓氏仍然突出显示，给人的印象是按钮仍然按下。
The VBA code behind is as follows.
后面的VBA代码如下。
This sits within the Sheet1 code:
这位于 Sheet1 代码中：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 4 And Target.Row <= Application.WorksheetFunction.CountA(Range("D:D")) Then
Range("D:D").Calculate
Call cfTest
End If
End Sub
And this is the method itself:
这是方法本身：
Sub cfTest()
Range("E:E").ClearContents
If ActiveCell.DisplayFormat.Interior.color <> 16777215 Then
ActiveCell.Offset(0, 1) = True
End If
End Sub
The application I ended up building off this example had much more too it, but going back to the posted question, the cfTest() method allowed me to test if a cell was conditionally formatted based upon cell fill.
我最终基于此示例构建的应用程序还有更多内容，但回到发布的问题，cfTest() 方法允许我测试单元格是否基于单元格填充有条件地格式化。
回答by Sam
I'm not sure as to the why of this but maybe it'll help. VB doesn't seem to allow access to a cells color when that color is based on conditional formatting.
我不确定这是为什么，但也许会有所帮助。当颜色基于条件格式时，VB 似乎不允许访问单元格颜色。
For example..
例如..
'cell A1 colored yellow through conditional formatting
MsgBox Range("A1").Interior.ColorIndex
'returns the incorrect result of -4142 regardless of cell color
'cell B1 colored yellow via the fill option on the ribbon
MsgBox Range("B1").Interior.ColorIndex
'returns the correct result of 6
That being said, is there a reason you couldn't just test the cell for whatever formatting rules you have in effect. That would eliminate the need for a UDF.
话虽如此，您是否有理由不能仅针对您具有的任何格式规则测试单元格。这将消除对 UDF 的需要。
=IF(A1<50,False,True)
回答by Chris Strickland
Here are two related functions that implement mathematical conditions. This is slightly less complicated than the Chip Pearson version, and also less complete, but I think this should cover most cases, and this shouldn't be too difficult to extend.
这是实现数学条件的两个相关函数。这比 Chip Pearson 版本稍微简单一些，也不太完整，但我认为这应该涵盖大多数情况，并且应该不会太难扩展。
Function isConditionallyFormatted(rng As Range) As Boolean
Dim f As FormatCondition
On Error Resume Next
isConditionallyFormatted = False
For Each f In rng.FormatConditions
isConditionallyFormatted = checkFormula(rng.Value, f.operator, f.Formula1)
isConditionallyFormatted = checkFormula(rng.Value, f.operator, f.Formula2)
Next
End Function
Function checkFormula(rng As Variant, operator As Variant, condition As Variant)
On Error GoTo errHandler:
Dim formula As String
condition = Right(condition, Len(condition) - 1)
Select Case operator
Case xlEqual: formula = rng & "=" & condition
Case xlGreater: formula = rng & ">" & condition
Case xlGreaterEqual: formula = rng & ">=" & condition
Case xlLess: formula = rng & "<" & condition
Case xlLessEqual: formula = rng & "<=" & condition
Case xlExpression: formula = condition
End Select
checkFormula = Evaluate(formula)
Exit Function
errHandler:
Debug.Print Err.Number & " : " & Err.Description
End Function
This will work for some common operators, but there are two other operators (xlBetween and xlNotBetween) and there are other types of condition that would have to be caught as well, and the logic for some of those would be a little more complicated than this. Some of them, however (like databars), inherently convey that there is a condition, so no processing would be necessary.
这适用于一些常见的运算符，但还有另外两个运算符（xlBetween 和 xlNotBetween），还有其他类型的条件也必须被捕获，其中一些的逻辑会比这更复杂一些. 然而，其中一些（如数据条）本质上表明存在条件，因此不需要处理。
Here is a link to the full documentation:
这是完整文档的链接：
回答by pascalb
I would perform a prior check for the color index your condition is for using this:
我会预先检查您的条件是使用此颜色索引：
Function cfTest_color_chk(inputCell As Range)
cfTest_color_chk = inputCell.Interior.ColorIndex
End Function
Then your function
然后你的功能
Function cfTest(inputCell As Range)
If inputCell.Interior.ColorIndex <> -4142 Then
cfTest = True
Else
cfTest = False
End If
End Function
Another solution to make things rock solid is to combine both function so that cfTest takes cfTest_color_chk as a parameter and cfTest_color_chk will return the value of the color to match...
另一种让事情变得坚如磐石的解决方案是结合这两个函数，以便 cfTest 将 cfTest_color_chk 作为参数，而 cfTest_color_chk 将返回颜色的值以匹配...
Hope this helps
希望这可以帮助
Pascal
帕斯卡
