Counting colored cells

Topic

New Reply

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.

Reply | Quote

Replies

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, _
                                     	HEADING_INTERIOR_COLORINDEX)


	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.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

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.

Reply | Quote

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?

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

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).

Reply | Quote

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, ExcelXLRangeLib_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.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

…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.

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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,) to get the count from the specific filter applied. But you can only do one filter at a time.

Reply | Quote