Help using Chip Pearsons code to count cells with color

  • Thread starter Thread starter ram
  • Start date Start date
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
 
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
 
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
 
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
 
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
Click to expand...
to
If IsValidColorIndex(ColorIndex) = False Then

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
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
 
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
 
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.
 
Back
Top