Counting by colour dilemma!!

[Simon Lloyd]

Thanks Cripbd for your reply, i don't know whether that will work a
when i try to run my Auto_open i get a runtime error 13 type mismatc
in the Msgbox line where i have cccount it shows error 2015 when th
cursor is placed over it......any ideas?


Simo

 

[Dave Peterson]

You won't want to refer to the activecell in your code that goes through a range
without selecting...

Function CountByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If IsError(Rng.Value) Then
'skip it
ElseIf IsDate(Rng) Then
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Function