Adding the Coloured Numbers...

Using a loop, a variable and some VBA magic to create a custom function
in Excel.

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!

A range of cells - some with the font coloured red

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.

Using the SUM function to add up the chosen cells

So, could he record a macro to sum just the cells with red numbers?

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.

How VBA Determines Font Colour

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:

? ActiveCell.Font.ColorIndex

Press Enter and see the Immediate Window display the palette index number of the colour you chose (e.g. 3 = red, 5 = blue).

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]...
Next Cell

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

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

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
      End If
   Next Cell
   SumRed = x
End Function

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.

Using the custom function SUMRED to sum a range of cells

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.

Excel's font colour picker Interestingly (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 click here. 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:

Sub FontColor()
   MsgBox "The Font Color Index is " & ActiveCell.Font.ColorIndex
End Sub

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.