#VALUE error when counting cells

Hi there,

It's almost impossible to suggest a solution without access to the workbook which contains the error, but the following code might provide a workaround in cases where the error is in fact generated by the data values:

Option Explicit


Public Function CountCells(rTarget As Range) As Long

    Dim rCell As Range

    Application.Volatile

    CountCells = 0

    For Each rCell In rTarget.Cells

        If IsError(rCell.Value) = False Then

            If rCell.Value = vbNullString Then

                With rCell.Interior

                    If .Pattern = xlSolid And _
                       .Color = RGB(146, 205, 220) Then

                        CountCells = CountCells + 1

                    End If

                End With

            End If

        End If

    Next rCell

End Function

Hope this helps,

Regards,

Greg M