Adding Up the Coloured Numbers
"How can I add up just the coloured numbers in a range of cells?"
It seems a perfectly reasonable request - or at least it did to the
person who asked me how to do it!
He had a spreadsheet showing invoice values. Some had been paid,
others had not. He had used the formatting tool to colour the unpaid
invoices red so that they could be seen at a glance. There was no
other reference on the sheet to indicate whether or not each invoice
had been paid, so automated techniques like Conditional Formatting
could not be used. He was happy to format the cells manually. That
was OK. They could be summed manually (time consuming in itself) but
each time another invoice was paid, and another cell coloured, the
formula had to be changed.
So, could he record a macro to sum just the cells with red
How Can We Tell Excel Which Cells to Add?
Excel must know that certain cells contain numbers that have been
formatted red. So, are there any tools or functions we could use to
tap into that knowledge?
Perhaps we could automatically select the cells - that often
overlooked Edit > GoTo > Special might have something... no.
How about functions... SUMIF allows you to sum a range of
cells that satisfy certain criteria - but even combined with a text
or information function there isn't anything that allows you to
refer to formatting of colour. DSUM isn't any help either. So
recording a macro is out of the question - we don't have anything
suitable to record.
But the numbers are red, and Excel must know they are red, so
there must be a way. The answer is to use VBA.
The Master Plan
My plan is to create a custom function (UDF) in VBA that will
just require the user to enter the range of cells to be treated -
something like =SUMRED(A2:B5).
The function will need to look at each cell and determine the
font colour. If it is red the value is added to a running total
until all the values in the red cells have been summed.
You can use VBA to determine the colour of the font in any
particular cell by using the ColorIndex property.
If you want to try it out, select a cell on your worksheet and
use the Font Color button on the toolbar to apply a colour of
your choice. Note that the colour picker shows the names of colours
but not their palette index numbers.
Now open the Visual Basic Editor (keys ALT+F11) and
open the Immediate Window (keys CTRL+G). In the
Immediate Window type:
Press Enter and see the Immediate Window display the
palette index number of the colour you chose (e.g. 3 = red, 5 =
Using a Loop to Move from Cell to Cell
If the user defines the range of cells to be summed, we can use a
loop to move through all the cells in that range. The For Each..
Next... loop is ideal for looping through a number of objects in
a collection - something like:
For Each Cell In Selection
...[Code Goes Here]...
This loop will cause Excel to consider each cell then move on to
the next and finish when all the cells in the selection have been
Using a Variable to Hold the Running Total
This is a good example of using a variable to hold a running
total. We must start by setting the value of the variable to zero:
x = 0
When each cell is examined, if it is a red cell its value can be
added to the variable:
x = x + Cell.Value
Putting It All Together
We just need to define the variables and devise an IF
Statement to determine the font colour. Here's the finished
Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font colour is red(3).
Dim Cell As Object
Dim x As Double
x = 0
For Each Cell In SelectedCells
If Cell.Font.ColorIndex = 3 Then
x = x + Cell.Value
SumRed = x
Now all the user has to do is enter the SumRed function
into a cell, specifying the range of cells to be summed. User
defined functions behave just like a built-in functions, so if the
cell values change or more cells are coloured red, the total shown by the
function will be automatically updated.
What About Other Colours?
I have used red in this example but, of course, you can apply it
to any colour by substituting the new colour's palette index number.
VBA identifies colours by their colorindex (note US English
spelling of the word "color"). The standard palette of colours in
Excel contains 56 colours, numbered 1 to 56.
(or infuriatingly!) the colour picker that appears when you click
the Font Color or Fill Color buttons shows only 40 of
these colours. If you go to Format > Cells the colour picker
offers a further 5 colours (Periwinkle (17), Ivory (19), Coral (22),
Ocean Blue (23), Ice Blue (24)). The 11 missing colours (18, 20, 21,
25 - 32) can only be applied using code. Perhaps offering a choice
of 56 colours would have made the colour picker too big (this is the
sort of question my students ask!).
To see an Excel colour palette showing the colour index numbers,
plus Excel's "hidden" colours
. The link will open in a new window.
What Colour is it Now?
So you think the SumRed function is great and you want to
use it. But you already coloured your numbers and is wasn't red. No
problem... you just change the ColorIndex value to the
appropriate number. But what is it? Two ways to find out:
- Go back and read the bit about using the Immediate Window
to test your VBA code and get information about your worksheet.
^ take me there
- Write yourself a short macro like this one:
MsgBox "The Font Color Index is " & ActiveCell.Font.ColorIndex
Select a cell and run the macro. You'll get a message telling you
the colour index number of the font. If you get the number -4105
it means "automatic", i.e. the font doesn't have a special colour.
For more information on creating custom functions, read the VBA
Writing Your First VBA Function