We often get asked if we can count colored cells on a worksheet, and yes you can.
You could count cells using a combination of conditional formatting, filters and the SUBTOTAL function, but I want to show you how writing a UDF (user defined function) can let you count, sum and average colored cells, in the same function.
Background Color or Conditional Formatting?
Before we get started I need to say that this UDF works only with cells that have their background (fill) color changed. It does not work with conditional formatting.
Before you start rolling your eyes, this limitation is down to Microsoft. You can use VBA to get the color of the cell if it is conditionally formatted:
CellColor = ActiveCell.DisplayFormat.Interior.Color
This works fine in a Sub. But put that same line into a function, and you get a #VALUE error returned.
Why? Ask Microsoft. Their documentation just states that the DisplayFormat property will generate a #VALUE error in a UDF.
So we'll just work with background color for now. I will write another blog post on conditional formatting and provide code that works with CF.
We want to compare the color in a reference cell against the colors of a range of cells. So we must pass into the function both of these things. The function is called ColorMath.
Function ColorMath(InputRange As Range, ReferenceCell As Range, Optional Action As String = "S")
InputRange is the first argument and is the range of cells we are checking.
ReferenceCell is the cell with the color we are looking for.
The 3rd argument is the Action we want to carry out, that is, are we going to SUM, COUNT or AVERAGE the values in the cells. This is an optional argument. If it is omitted then Action is given the default value "S" which indicates we are going to SUM values.
I've made Action a String, rather than a number as reading the formula in the sheet seems clearer to me if I see "S" for SUM, "C" for COUNT or "A" for AVERAGE. You can of course use numbers to indicate what you want your functions to do.
The string that specifies the action is not case sensitive. The function converts it to upper case before checking it.
Checking Fill Colors
The piece of code that checks the background or fill color is pretty straightforward.
The color of our ReferenceCell is assigned to the variable ReferenceColor by :
ReferenceColor = ReferenceCell.Interior.Color
Looking For The Colored Cells
Use a For ... Next loop to check each cell in the InputRange
If Action = "C" Then For Each Cell In InputRange If Cell.Interior.Color = ReferenceColor Then Result = Result + 1 Next Cell End If
This loop is counting the number of cells that match our ReferenceColour. The process is similar if we want to SUM or AVERAGE the matching cell values.
With a small list of numbers, colored as shown, I've set up my function to show me the SUM, COUNT and AVERAGE of the numbers in the three different colors.
These examples are contained in the sample workbook you can download below.
You can of course (and you should) use Named Ranges rather than specify the range as I have done in these examples.
My sample workbook uses a named range called ColoredCells.
If you change the fill color of a cell using the Ribbon, by right clicking and choosing Fill Color, or by right clicking and choosing Format Cells->Fill, this won’t trigger a recalculation, so a function's output may not be correct. Be wary of this.
This is down to the way Excel works. It just doesn't see a background color change as a change worthy of recalculating. Only certain things will cause Excel to recalculate.
However, using the Format Painter to change fill colors will cause a recalculation to occur.
You could also make use of the Workbook_SheetSelectionChange event to make Excel calculate as new cells are selected. Changing the fill color still won't cause a recalculation, but as soon as you click a different cell, or use the cursor keys, Excel will recalculate.
In the sample workbook below I've included code for the Workbook_SheetSelectionChange event if you want to use it.
Look in the ThisWorkbook module.
You can also make the function volatile, that is, its output is recalculated each time the sheet or workbook undergoes a recalculation.
By using the statement Application.Volatile we can make our function recalculate by pressing SHIFT+F9 to recalculate the active sheet.
I haven't written any specific error checking or handling routines. So if your InputRange contains a letter, you'll get a #VALUE error.
You can let Excel handle error processing, or write your own error handling routines.
Download Sample Workbook
Download the workbook here.
If you liked this or know someone who could use it please click the buttons below to share it.