测试Excel中单元格是否有条件格式的VBA函数 [英] VBA function to test if cell is conditionally formatted in Excel

问题描述

我已经写了下面的函数来测试一个单元格是否基于单元格填充激活条件格式。

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?

干杯。

编辑：更新功能但不适用于条件格式化

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

推荐答案

如果需要结果，这是一个工作演示。列E查看列D，如果通过单元格填充颜色有条件地格式化，则显示值TRUE。即点击名称Bob，有条件地格式化通过下面的代码突出显示单元格

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

=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.

后面的VBA代码如下。

The VBA code behind is as follows.

这位于Sheet1代码中：

This sits within the Sheet1 code:

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

这是方法本身：

Sub cfTest()

Range("E:E").ClearContents

If ActiveCell.DisplayFormat.Interior.color <> 16777215 Then
    ActiveCell.Offset(0, 1) = True
End If

End Sub

我最终建立这个例子的应用程序还有更多的东西，但是回到发布的问题，cfTest（）方法允许我测试一个单元格是否有条件地格式化填充单元格。

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.

这篇关于测试Excel中单元格是否有条件格式的VBA函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！