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


Simon Lloyd wrote:

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

Simon

--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=276337

--

Dave Peterson