Cannot replicate that MSDN message when downlaoding the module or importing
to a workbook.

Did you unzip then import the module to your workbook?

Try this standalone UDF also from Chip.

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'You can call this function from a worksheet cell with a formula like
'=COUNTBYCOLOR(A1:A10,3,FALSE)

Dim rng As Range
Application.Volatile True

For Each rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(rng.Interior.ColorIndex = WhatColorIndex)
End If
Next rng

End Function


Gord

On Tue, 16 Mar 2010 14:23:02 -0700, ram
wrote:

Thanks for your reply

I Download the whole module again this time i received the MSDN collection
does not exist. Please reinstall MSDN.

Thanks in advance for any help

"Gord Dibben" wrote:

Do you have the other functions that Chip provides?

The COUNTCOLOR function won't work on its own.

You should download the entire module from Chip's site so's you get all
pertinent functions.

This is posted at the site......................

A NOTE ABOUT THE VBA CODE MODULE: The modColorFunctions downloadable module
contains approximately 20 color-related functions. These function call upon
one another, so you should Import the entire module into your VBA Project
rather than pasting in only individual functions. If you don't import the
entire module, you may get errors reporting undefined function names.

......................................


Gord Dibben MS Excel MVP

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

.