Need a UDF in Excel - Identify Cells with Conditional Formatting Fill Color

[Capwn3]

Need a UDF in Excel to determine if any cells in a row contain fill color as a result from conditional formatting.

I’ve tried many different attempts from similar needs found on the internet. But I either get all cells’ results to be either all TRUE or all FALSE regardless if it contains the conditional formatting fill color or not OR I get all cells’ results to show #VALUE!.

I’ve tried numerous VBA coding. I expected to see in the cell at the end of each row to show or indicate if any of the cells in the row have fill by color from conditional formatting so I can easily filter on only the ones that do for easier processing.

Please help!

 

[James006]

Hi,
Just relaying following solution to find out if cell is Conditionally Formatted ... or not ...

Function isConditionallyFormatted(rng As Range) As Boolean
' Author : Chris Strickland - adapted from Chip Pearson
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 f
End Function

Function checkFormula(rng As Variant, operator As Variant, condition As Variant)
'Author : Chris Strickland - adapted from Chip Pearson
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

 

[Capwn3]

Hi,
Just relaying following solution to find out if cell is Conditionally Formatted ... or not ...

Function isConditionallyFormatted(rng As Range) As Boolean
' Author : Chris Strickland - adapted from Chip Pearson
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 f
End Function

Function checkFormula(rng As Variant, operator As Variant, condition As Variant)
'Author : Chris Strickland - adapted from Chip Pearson
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

Thank you VERY MUCH sir! I’m a bit green with this, so which formula/function do I place in the helper cell to check the entire row’s each cell (D3:V25)?

 

[Capwn3]

Thank you VERY MUCH sir! I’m a bit green with this, so which formula/function do I place in the helper cell to check the entire row’s each cell (D3:V25)?

I meant (D3:V3) I tried both functions and get either all FALSE even if full color from conditional formatting is present or not or the #VALUE! error.

 

[James006]

Hi,
The first test is to add, for example in cell D10, or in any cell of your choice

=isConditionallyFormatted(D3)

and copy this formula till Column V

Hope this will help

 

[James006]

Once you have tested this UDF ... feel free to come back to the Forum

 

[Capwn3]

Once you have tested this UDF ... feel free to come back to the Forum

Thank you for all your help! Not sure what is wrong but just like with all the other UDF’s that were tried, I input the formula (=isConditionallyFormatted(D3)) and there is definitely a pink fill from conditional formatting in that cell but I get a FALSE. I copied down in the helper cell, all cells’ results are FALSE. This is the behavior I get no matter what UDF for this I’ve tried so far. I’m using Excel 2016. Any idea why?