#VALUE error when counting cells

[Afjio12]

Hello,

I use the code below to count cells with a certain fill color and when they are empty.
When i try this in an empty sheet it works perfectly but when i implement this into a big sheet where the sollution is for i get a #VALUE error.
There is no error in the range so that cannot be the problem. I know it is hard to find a sollution when the real workbook isn't provided but i realy can't upload that.
Does anyone know what can trigger this error? I added a example sheet if the cell count sollution isn't clear to anyone.

TX!

Public Function CountCells(RNG As Range) As Double
    Application.Volatile
For Each CL In RNG
    If IsEmpty(CL) = True Then
        With CL.Interior
            If .Pattern = xlSolid And .Color = RGB(146, 205, 220) Then
                CountCells = CountCells + 1
            End If
        End With
    End If
Next CL


End Function

[Greg M]

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