CODEWITHSUNDEEP
excelvbafunctionudf
Orin Moyer
Orin Moyer

Reputation: 509

Custom excel formula function UDF to count Conditional Formatting

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

Upvotes: 3

Views: 185

Answers (1)

Tim Williams
Tim Williams

Reputation: 166685

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")

Upvotes: 3

Related Questions