VBA function reading all cells as -4142

[Chris J.]

Hi,

I'm counting the number of Light Turquoise cells in a
column on a worksheet, I know that my function works, but
it is picking up all the cell fill colors as -4142, when i
know that they are 34. Is there some option I have to turn
on to get the functions to read formatted cells?

Or am I doing something else wrong?

My function:

Function CountBlueCells(InputRange As Range) As Long
Dim cl As Range
Dim NumberOfBlueCells As Integer
Application.Volatile
On Error Resume Next ' ignore any errors
For Each cl In InputRange
If (cl.Interior.ColorIndex = 34) Then
NumberOfBlueCells = NumberOfBlueCells + 1
End If
Next cl
On Error GoTo 0
CountBlueCells = NumberOfBlueCells
End Function

cheers,
Chris

 

[J.E. McGimpsey]

As a guess, and assuming you're passng a valid range, are the cells
colored using Conditional Formatting?

If so, VBA has no easy way of determining whether CF is applied or
not. Instead, use the same condition that you used in the CF
criterion.