How to count only visible cells in a custom function (count cells based on cell colour)

Question

I am using a custom function that I found on the net. It does what I need it to do - counts cells of specific colour in a range of cells.

How do I edit it to only count visible cells? I have tried to use this, but it doesn't doo anything.

For Each rCell In CountRange.SpecialCells(xlCellTypeVisible)

Complete function is this:

    Function GetColorCount(CountRange As Range, CountColor As Range, Optional VolatileParameter As Variant)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

To use it in the worksheet I then type:

=GetColorCount(A1:G20,H1, NOW())

Where:

• A1:G20 is the range I want to count all yellow cells
• H1 is cell where count is displayed and is coloured in yellow
• NOW() makes it run everytime a change is made in the range (??)

Any tips?

Answer 1

Function GetColorCount(CountRange As Range, CountColor As Range, Optional VolatileParameter As Variant)
 Dim ColVal As Long, rCell As Range
 Dim TotalCount As Long
 ColVal = CountColor.Interior.ColorIndex
    For Each rCell In CountRange.Cells
      If rCell.Interior.ColorIndex = ColVal Then
          If rCell.EntireRow.Hidden = False And _
              rCell.EntireColumn.Hidden = False Then
              TotalCount = TotalCount + 1
          End If
      End If
    Next rCell
 GetColorCount = TotalCount
End Function

SpecialCells cannot be used in UDF, but checking of each cell in the range visibility is possible...

Answer 2

SpecialCells is indeed flaky when wrapped in a UDF like this.

Here is a solution/workaround by checking the row height and column width for zero. I'm sure others exist but this is one way of achieving your goal.

Function GetColorCount(CountRange As Range, CountColor As Range, Optional VolatileParameter As Variant)
    Dim CountColorValue As Integer
    Dim TotalCount As Integer
    CountColorValue = CountColor.Interior.ColorIndex
    For Each rcell In CountRange.Cells
        If rcell.Interior.ColorIndex = CountColorValue Then
            If (rcell.EntireRow.Height * rcell.EntireColumn.Width) <> 0 Then
                TotalCount = TotalCount + 1
            End If
        End If
    Next rcell
    GetColorCount = TotalCount
End Function