1. Hi loungers......I suspect that this is a rather difficult topic, but I am looking for either VBA or a 'regular' COUNT or COUNTIF function (??) that I can use to count the number of cells, based on color..........eg: if I have 23 red cells (red as the interior color), then I suspect that I need something that counts the interior.colorindex = 3.............I want to be able to color different cells manually, and count how many of each color I have......I have checked this lounge and can't find very much so far. I have also checked Chip Pearson's site but didn't find anything that I could understand well enough to be able to use......any suggestions? Thanks.

2. Counting cells of a given color is not at all as easy as you might think. Even with my fairly significant experience in this area, I had to do a tad of experimenting to work out how to get the count. To make matters worse, the documentation covers colors of objects in worksheets very poorly, IMO.

Unfortunately, you will need to do a bit of experimenting, but I've given you a huge leg up with the following information and the attached example workbook.

Function lngCountCellsOfAColor, shown in Listing 1, is the color counter. Immediately following it, Listing 2 shows the enumeration used to define the valid values of its third argument, penmWhichColor. In the context of a worksheet, the valid values are as follows.

FontColor = 1
BackgroundColor = 2

Assuming that you want to call this function from a worksheet cell, the next hurdle is how to specify the range. To date, the only way I've been able to successfully pass a range to a function is by passing the sheet name and the range name in separate arguments. I'm sure there are more elegant ways to do it, using the Names collection, but, to date, I haven't been sufficiently motivated to delve into that. Therefore, the first and second arguments, pstrSheetName and pstrRanngeName, respectively, are strings.

Finally, we come to the most challenging argument of all, pvarColor, which is defined as a Variant, and sparsely documented beyond that. To date, I've found that the simplest way to get a usable ColorIndex is to get the ColorIndex of a cell to which the desired color has been applied. To that end, I give you GetCellColor, shown in Listing 4, which is a primitiive, but workable subroutine, intended to be run from the Immediate window. The idea is that you hard code the name of the sheet, the coordinates of a cell of the desired color, and the enmerated constant to indicate whether you want the font color or the interior color. The value returned in the message box is the color index that you feed to the cell counter function.

Listing 3 is a demonstration macro, imaginatively named main, which demonstrates lngCountCellsOfAColor by counting the cells in the heading of the KEYS worksheet in the attached demonstration workbook, which also contains a compiled, but unsigned, copy of all of the code shown here.

In case you are wondering why the KEYS worksheet is empty, except for the headings and formatting, it is a gutted copy of a production worksheet that contains such things as my login password for this board.

Code:
```Public Function lngCountCellsOfAColor( _
pstrSheetName As String, _
pstrRanngeName As String, _
penmWhichColor As enmColorProperties, _
pvarColor As Variant) _
As Long

'   Function Name:  	lngCountCellsOfAColor
'   Synopsis:       	Count cells whose interior or font color matches a given
'                   	color index value.
'
'   In:             	pstrSheetName   = Worksheet Name
'                   	pstrRanngeName  = Name of Range, which must be a Named,
'                                     	or predefined, Range
'                   	penmWhichColor  = A enmColorProperties value, as follows:
'                                       	FontColor   	= 1 = Font (text)
'                                                             	color
'                                       	BackgroundColor = 2 = Background
'                                                             	(interior)
'                                                             	color
'                   	pvarColor   	= ColorIndex value, which is defined as
'                                     	a Variant.
'
'   Out:            	Count of cells having the specified color.
'
If pstrSheetName <> vbNullString And pstrRanngeName <> vbNullString And penmWhichColor <> ValueNotSet Then
Dim rng As Range
Set rng = ActiveWorkbook.Worksheets(pstrSheetName).Range(pstrRanngeName)

Dim intTotCols As Integer: intTotCols = rng.Columns.Count
Dim lngTotRows As Long: lngTotRows = rng.Rows.Count

Dim intCurrCol As Integer
Dim lngCurrRow As Long

Dim rngThisCell As Range

Dim lngWorkingCount As Long

For lngCurrRow = 1 To lngTotRows
For intCurrCol = 1 To intTotCols
Select Case penmWhichColor
Case FontColor
If rng.Cells(lngCurrRow, intCurrCol).Font.ColorIndex = pvarColor Then
lngWorkingCount = lngWorkingCount + 1
End If
Case BackgroundColor
If rng.Cells(lngCurrRow, intCurrCol).Interior.ColorIndex = pvarColor Then
lngWorkingCount = lngWorkingCount + 1
End If
End Select
Next intCurrCol
Next lngCurrRow

lngCountCellsOfAColor = lngWorkingCount
Else
lngCountCellsOfAColor = 0
End If

End Function```
Listing 1 is my color counter, lngCountCellsOfAColor. As written, it can count cells that are set in a given foreground (font) or background (interior) color.

Code:
```	Public Enum enmColorProperties
ValueNotSet
FontColor
BackgroundColor
End Enum```
Listing 2 is the enumeration used to indicate, via the penmWhichColor argument, which part of the cell has the ColorIndex value specified by the fourth argument, pvarColor.

Code:
```Sub main()

Const HEADING_INTERIOR_COLORINDEX As Integer = 55
Const KEYS_SHEET_NAME As String = "KEYS"
Const KEYS_ALL_RANGE_NAME As String = "rngEverything"

Dim lngCellCount As Long
lngCellCount = lngCountCellsOfAColor(KEYS_SHEET_NAME, _
KEYS_ALL_RANGE_NAME, _
BackgroundColor, _

MsgBox "The interior color of the heading cells in sheet " _
& KEYS_SHEET_NAME & " is " & HEADING_INTERIOR_COLORINDEX & vbLf _
& lngCellCount & " cells are this color.", _
vbInformation, _
ActiveWorkbook.Name

End Sub```
Listing 3 demonstrates that the counting function works.

Code:
```Sub GetCellColor()

Const MY_SHEET_NAME As String = "KEYS"
Const ROWINDEX As Long = 1
Const COLINDEX As Integer = 1

Dim WhichPart As enmColorProperties: WhichPart = BackgroundColor

Const MAX_COLS As Integer = 256
Const MAX_ROWS As Long = 65536

If ROWINDEX > MAX_ROWS Then
MsgBox "The specified row index of " & ROWINDEX & " is too large." & vbLf _
& "The value must be a whole number between 1 and " & MAX_ROWS, _
vbExclamation, _
ActiveWorkbook.Name
Else
If COLINDEX > MAX_COLS Then
MsgBox "The specified column index of " & COLINDEX & " is too large." & vbLf _
& "The value must be a whole number between 1 and " & MAX_COLS, _
vbExclamation, _
ActiveWorkbook.Name
Else
Dim strColorName As String
Dim fShowMe As Boolean
Dim wksThis As Worksheet
Dim varColor As Variant

Select Case WhichPart
Case FontColor
fShowMe = True
strColorName = "FontColor"
Set wksThis = ActiveWorkbook.Worksheets(MY_SHEET_NAME)
varColor = wksThis.Cells(ROWINDEX, COLINDEX).Font.ColorIndex
Case BackgroundColor
fShowMe = True
strColorName = "BackgroundColor"
Set wksThis = ActiveWorkbook.Worksheets(MY_SHEET_NAME)
varColor = wksThis.Cells(ROWINDEX, COLINDEX).Interior.ColorIndex
Case Else
MsgBox "The specified value of variable WhichPart, " & WhichPart _
& " is invalid. Valid values are 1 and 2.", _
vbExclamation, _
ActiveWorkbook.Name
End Select

If fShowMe = True Then

MsgBox "The " & strColorName & " of cell (" & ROWINDEX & ", " & COLINDEX & ") of worksheet " _
& wksThis.Name & " in workbook " & ActiveWorkbook.FullName & " is " & varColor, _
vbInformation, _
ActiveWorkbook.Name
End If
End If
End If

End Sub```
Listing 4 is a utility macro, for quickly identifying the colorindex of the cells of interest.

3. A couple of functions to get the number of cells within a range with either the same background color as a reference cell, or with an absolute color value. As mentioned, the change of a background color doesn't trigger recalculation, so you have to force it with Ctrl-Alt-F9.

4. The Chip Pearson functions should work. You don't really have to understand them to use them. You just have to add the functions to a module and then use them as described by Pearson...

Steve

5. ...wow....a lot of stuff going on here, and I thank you for your replies and help.

I think that I might have found the answer (I hope).........I am using a free Excel add-in utility called ASAP-Utilities (designed by Bastien Mensink from, where else, the Netherlands...)...) I have used it for a couple of years and it is outstanding....after I posted my question, I thought that I should take a look at it for help. I discovered, early this morning, that the most recent upgrade now contains a 'utility' that counts cells by interior color.....you have to 'enter' the function using ASAP (for red cells, it would be =ASAPCOUNTBY CELLCOLOR(range,3) where 3 is from the color pallette.......because it is pasrt of ASAP, I imagine that you have to download and install ASAP and add it to Excel, but it works like a charm.....you do have to hit F9 to force the calculations, but that seems to be the case even with the most complex VBA/functions when it comes to counting colors........in my case, I want to color the cells manually and then count the reds or greens etc. There is also an ASAP formula that will count by font color.

Neither of these seem to work if the cell/font is colored by conditonal formatting, but I haven't played with it enough to know if it will.....however, in the meantime, this works like a charm for me.........thanks, again, for your replies and I hope that my reply will help others.

6. Conditional formatting is different than explicit Chip Pearson has functions for reading those as wellhttp://www.cpearson.com/excel/CFColors.htm

Steve

7. Originally Posted by dmcnab
Neither of these seem to work if the cell/font is colored by conditonal formatting
If you are coloring cells by conditional formatting, you should be able to create a formula to count the colored cells.

8. If the conditional formatting is consistent over the range, it would be more efficient to just test the cells content and compare to the condition to count the cells meeting the various conditions and ignore trying to figure out the cell's conditional color...

Steve

9. Originally Posted by Stephane Quenson
A couple of functions to get the number of cells within a range with either the same background color as a reference cell, or with an absolute color value. As mentioned, the change of a background color doesn't trigger recalculation, so you have to force it with Ctrl-Alt-F9.
I see that your CountBackgroundColorFromRefCell and CountBackgroundColorFromColor functions take Range objects, and that they appear to work with simple, local range definitions. I've never been able to get them to work for me, but that may be because I usually use Named Ranges. How do they behave with Named Ranges, absolute addresses, and addresses of ranges in other sheets?

10. I don't know how the functions will work in such cases and cannot test it right now being away from my main computer but I guess it should work. If it doesn't, it's not my fault but Microsoft's fault to not make all ranges equal in functions!

11. Originally Posted by dmcnab
I am looking for either VBA or a 'regular' COUNT or COUNTIF function (??) that I can use to count the number of cells, based on color
I'm late to this party, and I don't think my suggestion fits your requirement, but in Excel 2007, Data | Filter will filter on color coding - you can then run =SUBTOTAL(2,<range>) to get the count from the specific filter applied. But you can only do one filter at a time.

12. Originally Posted by David Gray
How do they behave with Named Ranges, absolute addresses, and addresses of ranges in other sheets?
The functions Stephane posted approximate typical Excel functions in that they can be fed a range reference (single cell or more).

They won't work as written directly with Named Ranges; would have to use the same technique you use to convert the named range string .Range("MyNamedRange") to an object model range. They will work fine with absolute addresses, and addresses of ranges in other sheets if the other sheet range is correctly specified in the argument(s).

13. Originally Posted by David Gray
How do they behave with Named Ranges, absolute addresses, and addresses of ranges in other sheets?
They work well, at least in Excel 2003, I just tested the formulas: =CountBackgroundColorFromColor(MyNamedRange, 0), CountBackgroundColorFromColor(\$A\$45:\$F65,0), CountBackgroundColorFromColor(Sheet2!A1:B33,0) and they return proper results.

14. Originally Posted by JohnBF
The functions Stephane posted approximate typical Excel functions in that they can be fed a range reference (single cell or more).

They won't work as written directly with Named Ranges; would have to use the same technique you use to convert the named range string .Range("MyNamedRange") to an object model range. They will work fine with absolute addresses, and addresses of ranges in other sheets if the other sheet range is correctly specified in the argument(s).
I thought so, because I spent many unbilled hours in 2003, trying to get a worksheet function to treat a named range as an object model range before I gave up and wrote rngRangeFrmAddr_P6C. I subsequently discovered the Names collection, but I've never gone back to see whether it could be used to simplify rngRangeFrmAddr_P6C, because, after all, it worked as written, and was easy enough to use in VBA code, which was my chief design goal for it. To date, I've never used it directly from a worksheet, although I've made some indirect use of it, in functions that were designed as custom worksheet functions.

FWIW, attached archive, Excel\XLRangeLib_WW.ZIP contains a module, XLRangeLib_WW.BAS, which includes rngRangeFrmAddr_P6C, along with other functions that do such things as creating a new Range object from a rectangular region of an existing Range object. The archive also contains another module, WWXLAppExceptions.CLS, and a workbook, ErrWks.XLS, comprised of two worksheets, XLRangeLib_WW and wsWWXLAppErrors, To use the functions in XLRangeLib_WW.BAS, you must import the class module, WWXLAppExceptions.CLS, and one of the two worksheets, XLRangeLib_WW. The other sheet, wsWWXLAppErrors, was already in the workbook, so I left it as a second example of how to use the WWXLAppExceptions class. I didn't put the modules into a workbook, because I thought it would be easier to import them from the VBA module files, since there doesn't seem to be a good way to copy modules from one project to another in the VBA code editor.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•