Help using Chip Pearsons code to count cells with color

R

ram

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
 
G

Gord Dibben

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
 
R

ram

Thank you for your response,

I made the change, however I still can't get the code to work for me. I now
have a compile error: Sub or Function not defined.

Thanks in advance for any help
 
R

ram

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
 
C

Chip Pearson

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
 
G

Gord Dibben

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
 
C

Chip Pearson

If you want, you can email the actual module file you are using and
I'll take a look at it. I just download the file from the web site and
the code works exactly as intended. As Gord said, you need to import
the entire module, not individual procedurse since the procedures make
calls among themselves.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
R

ram

After I downloaded the complete moduel and again the correct way everything
worked well

Thanks to all of you for your time


Thanks Chip, this code is much appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top