Custom excel formula function UDF to count Conditional Formatting

Question

Has anyone run across a function that will actually work with conditional formatting?

there are some addons for kutools and albebits but they are not formula based (you have to select everything manually)

I have found this, but only works with manual formatting

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
   For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
            vResult = WorksheetFunction.SUM(rCell) + vResult
    End If
   Next rCell
Else
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
            vResult = 1 + vResult
    End If
   Next rCell
End If
ColorFunction = vResult
End Function

Tim Williams · Accepted Answer · 2016-08-03T23:36:12.897

3

Following on from @Jeeped and @Comintern...

This works for me - a simplified example:

Function WrapCountReds(rRange)
    WrapCountReds = rRange.Parent.Evaluate("CountReds(" & _
                          rRange.Address(False, False) & ")")
End Function

'can't call this directly from a worksheet but can be called via evaluate
Public Function CountReds(rRange As Range)

    Dim rCell As Range
    Dim vResult

    For Each rCell In rRange
      If rCell.DisplayFormat.Interior.ColorIndex = 3 Then
             vResult = 1 + vResult
      End If
    Next rCell

    CountReds = vResult
End Function

Worksheet usage example:

=WrapCountReds("A1:A100")

edited Aug 03 '16 at 23:36

answered Aug 03 '16 at 19:26

Tim Williams

154,628
8
97
125

1

Interesting. I never would have thought to use Evaluate as a wrapper function. – Comintern Aug 03 '16 at 20:45
1

@Comintern - related: https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 – Tim Williams Aug 03 '16 at 20:47

Custom excel formula function UDF to count Conditional Formatting

1 Answers1