Don't manually count colored cells do it with an easy-to-use macro

Don’t manually count colored cells—do it with an easy-to-use macro

by Susan Sales Harkins and Mike Gunderloy

Application:

Microsoft Excel 2000/2002/2003/2004/2007

Operating Systems:

Microsoft Windows, Macintosh

So you’ve created a color-coded spreadsheet to help you differentiate your data, and it works great. But it’s only the first step, because now you’re straining your eyes to count the entries assigned to each color. And if those colors change as your spreadsheet changes, you’ll need to perform yet another count. This simple macro will perform those counts for you.

To create this color-counting macro, we’ll:

• Create range names that correspond with the ones your macro uses.

• Add a command button to your worksheet so you can execute the macro with one click.

• Create the macro and get an idea of how its construction works.

• Brainstorm ways you can adapt this macro to count other cell properties.

You can’t solve every problem with a formula. For instance, counting items in a range is a simple task using the COUNT function, as long as you’re counting the contents of the cells. But what if you want to count the number of cells that are a certain color? The COUNT function can’t handle that task, but a macro can.

Figure A shows two ranges: One serves as a legend for the colors that appear in column B. Our macro technique counts the number of times a color in the legend occurs in the column B range.

A:

Our macro counts the number of times each color in the legend occurs in column B.

Automate your color legend

Why use a macro rather than construct the legend by hand? Because with the macro, you can automatically update the legend when the data in the cells of interest changes. In this article, we’ll attach the macro to a button, but there are other alternatives:

• You could run the macro every time you open the workbook.

• You could run the macro every time you recalculate the workbook.

• You could run the macro from a dedicated menu item.

In each case, you’d use the same VBA code for the macro; it’s just a matter of hooking up the code to the proper event of the appropriate object.

The macro in a nutshell

The macro compares each cell to the legend and then returns a value for each color to the right of the legend. That value represents the number of times the color occurs in column B. We’ll refer to these two ranges as ColoredCells and Legend, and we’ll create named ranges to represent them.

Set up the color-coded range and legend

First, let’s set up the color-coded range, which we’ll automatically count with our macro.

Download: You can save some time by downloading and extracting the sample file ColorCount.xls from the URL given at the beginning of the article. If you’d like to see the completed macro, check out Macro.xls.

To create the sample worksheet:

1. Enter the text and column headings, as shown in Figure A. (Leave column G blank for now.)

2. Use the Fill Color button fill color on the Formatting toolbar to color-code the cells in columns B and E. We used green for completed tasks, yellow for those in progress, and red for tasks that haven’t been started.

Note: Be sure to use the same colors in both ranges. The macro can detect the difference between shades, and it may produce an inaccurate count. That is, if you choose one green for the colored cells and a lighter shade of green for the legend, the macro won’t see any cells that match.

Create range names

The next step is to name the two ranges. This allows the m[...]