ColorFunction Question???

[slinky192001]

Hello,

Before i start, i dont know much about VBA, i have basically copied this from a website which is why it may sound like i have no idea what i am talking about (because i dont )

I copied the following into a module:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com

'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''

lCol = rColor.Interior.ColorIndex

If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If

ColorFunction = vResult
End Function


This works is i use the following to count the number of cells that are coloured the same as cell b76 in range c5 - c66
=colorfunction(C5:C66,B76)

The issue i have is, my coloured cells can sometimes also contain text. When this happens the formula reverts to #Value!

Cany anyone assist me with this please so that i can still count the number of coloured cells regardless of whether or not they also contain text?

Thank You in Advance

C

 

[VoG]

That works for me with cells containing anything.

 

[slinky192001]

Doh! (_8(I)

Got the formula backwards when i retyped it.

Working now, thank you.