You changed the code in the downloadable module. There is no such
function named "IsValid". There is a function named
"IsValidColorIndex" which ensures that a value is a valid colorindex
value. CHange
If IsValid(ColorIndex) = False Then
to
If IsValidColorIndex(ColorIndex) = False Then
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
On Tue, 16 Mar 2010 12:38:01 -0700, ram
wrote:
HI,
I am having trouble using Chip Pearson code for counting cells with color. I
receive the following error message Sub function not defined and it points
to Case Else
If IsValid(ColorIndex) = False Then
If you can tell me what I'm doing wrong please.
Thanks for any help
Function CountColor(InRange As Range, ColorIndex As Long, _
Optional OfText As Boolean = False) As Long
''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' CountColor
' This function counts the cells in InRange whose ColorIndex
' is equal to the ColorIndex parameter. The ColorIndex of the
' Font is tested if OfText is True, or the Interior property
' if OfText is omitted or False. If ColorIndex is not a valid
' ColorIndex (1 - 56, xlColorIndexNone, xlColorIndexAutomatic)
' 0 is returned. If ColorIndex is 0, then xlColorIndexNone is
' used if OfText is Fasle or xlColorIndexAutomatic if OfText
' is True. This allows the caller to use a value of 0 to indicate
' no color for either the Interior or the Font.
''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Dim R As Range
Dim N As Long
Dim CI As Long
If ColorIndex = 0 Then
If OfText = False Then
CI = xlColorIndexNone
Else
CI = xlColorIndexAutomatic
End If
Else
CI = ColorIndex
End If
Application.Volatile True
Select Case ColorIndex
Case 0, xlColorIndexNone, xlColorIndexAutomatic
' OK
Case Else
If IsValid(ColorIndex) = False Then
CountColor = 0
Exit Function
End If
End Select
For Each R In InRange.Cells
If OfText = True Then
If R.Font.ColorIndex = CI Then
N = N + 1
End If
Else
If R.Interior.ColorIndex = CI Then
N = N + 1
End If
End If
Next R
CountColor = N
End Function