Microsoft Excel - Count coloured cells

Most companies have some sort of backup strategy in place and that strategy often involves documenting the success & failure rate of the backups on a daily basis. For obvious reasons all the companies I've worked with in the past have documented this using a variation of the well-known "backup matrix", created using Microsoft Excel.

I choose to create my backup matrix documents so that backup results are colour-coded. That way they're easily visible and it's easy to see what happened when and to get an overall idea of how good your backups have been. Green coloured cells are successful backups, red cells are failed backups etc.

The first thing I need to do when I create these documents is to make some 'reference cells'. These cells are coloured with the various colours that will be used in the matrix, e.g. green for successful etc, as listed above. So they don't look like reference cells I also use them as the key for the matrix so people know what the various colours mean.

How, then, do you work out how good the backups have been if there's no text in the cells to look for? You need a VBA function that looks for the cell's colour instead of its contents - easy. Below is the function I wrote for that purpose - you'll need to make sure you are using .XLSM documents if you are using Excel 2007 for Windows as .XLSX files are, by default, secured so that macros won't run.

Function countColours(colourReferenceCell As Range, cellRange As Range)
    ' countColours
    ' Chris Rasmussen, April 2010
    '
    ' Count occurences of cells that are a certain background colour
    ' E.g. for use in a backup matrix
    
    Dim currentCell As Range
    Dim colourReference As Long
    Dim vResult
    
    colourReference = colourReferenceCell.Interior.ColorIndex
    
    ' reset the total count back to zero to prevent errors
    vResult = 0
    
    ' go through all the cells in the specified range and look at the background colour
    For Each currentCell In cellRange
        If currentCell.Interior.ColorIndex = colourReference Then
            vResult = vResult + 1
    End If
    Next currentCell
    
    countColours = vResult
    
End Function

Once you've got this function in place, all you need to do is choose the cell that will show the total of successful backups (for example) and enter the following formula. We'll assume that cell A1 is the cell that is coloured green for reference. We'll also assume that the Excel cell range C1:C5 contains the results.

If you have 5 successful backups in your range and they're all coloured green, the cell containing that formula with contain the number 5. Easy. :)