VBA function to test if cell is conditionally formatted in Excel

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.

The VBA code behind is as follows.

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 cfTestEnd IfEnd Sub

And this is the method itself:

Sub cfTest()Range("E:E").ClearContentsIf ActiveCell.DisplayFormat.Interior.color <> 16777215 Then    ActiveCell.Offset(0, 1) = TrueEnd IfEnd 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.

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.

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)        NextEnd FunctionFunction 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 FunctionerrHandler:    Debug.Print Err.Number & " : " & Err.DescriptionEnd 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.

Here is a link to the full documentation:

http://msdn.microsoft.com/en-us/ff835850(v=office.15)

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.

For example..

'cell A1 colored yellow through conditional formattingMsgBox Range("A1").Interior.ColorIndex'returns the incorrect result of -4142 regardless of cell color'cell B1 colored yellow via the fill option on the ribbonMsgBox 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.

=IF(A1<50,False,True)