Counting by colour dilemma!!
 

Function CntByColor(InRange As Range, WhatColorIndex As Integer
Optional OfText As Boolean = False) As Long
Dim Rng As Range
Dim cccount As Integer
Application.Volatile True
Range("D14:AI491") = Rng
For Each Rng In InRange.Cells
If OfText = True Then
IF APPLICATION.WORKSHEETFUNCTION.ISNA(ACTIVECELL.VALU E) THE
Range("B6").Value = Range("B6").Value - 1
CntByColor = CntByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CntByColor = CntByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng

End Functio

--
crispb
-----------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088
View this thread: http://www.excelforum.com/showthread.php?threadid=27633