Your CntByColor code won't work. You can only return a value to the worksheet
that holds the formula.

So when you try to do this:
Range("B6").Value = Range("B6").Value - 1
It'll blow up real good!

I'm not quite sure I understand, but maybe this'll be closer:

Option Explicit

Function CntByColor(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 Rng.Text = "#N/A" Then
'do nothing
Else
If OfText = True Then
CntByColor = CntByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CntByColor = CntByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If

Next Rng

End Function



Simon Lloyd wrote:

Thanks form the reply david, my count by color only if a date exists
works fine, it's the cntbycolor if cell contains text not #N/A over a
different range that does not work.....i have tried and tried but
cannot get it to work!

Any thoughts?

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