Use Colorfunction and Subtotal togheter to count filtered coloured cells

[Dome92]

Hi all!

I'm working on a spreadsheet and I managed to create a macro on VBA to count coloured cells as below:

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


And to use it I'm using the formula =colorfunction(A1;A12:A1459;FALSE), with A1 that is the coloured cell that I want to count.

The next step is being able to count coloured cells that are filtered, like a Subtotal formula that could work with the colourfunction.

Could you help me on this?

Thank you very much,

Domenico

[YasserKhalil]

Try this

Please Login or Register  to view this content.