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