| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help using Chip Pearsons code to count cells with color
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using Chip Pearsons code to count cells with color
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using Chip Pearsons code to count cells with color
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 . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using Chip Pearsons code to count cells with color
I think i figured out what I was doing wrong.
Thanks again for all your help Ram "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 . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using Chip Pearsons code to count cells with color
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using Chip Pearsons code to count cells with color
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 . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help using Chip Pearsons code to count cells with color
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. "Chip Pearson" wrote: 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I color code comments in cells on Excel spreadsheet? | Excel Worksheet Functions | |||
| Count cells with fill color and retain code - Mr. Umlas Can you he | Excel Discussion (Misc queries) | |||
| Chip Pearsons "Rowlander" add-in won't install | Excel Discussion (Misc queries) | |||
| How to color automatically color code sums in cells | Charts and Charting in Excel | |||
| Color code cells | Excel Worksheet Functions | |||
Linear Mode
