Counting by colour dilemma!!

[Simon Lloyd]

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

Any thoughts?


Simo

 

[Dave Peterson]

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