MeadInKent
| Feedback | Conditional Formatting |

Calculating values of coloured cells

[1] To write your own VBA function, open Tools | Macros | Visual Basic Editor. In the small project window select either the current spreadsheet file name or the personal.xls file and then Insert | Module.

This is a peculiar topic but is one that has been requested by several people. The goal is to sum all of the values of cells which are a particular colour. There is not an Excel function which returns a color value and it is necessary to create a user-defined function using VBA. Once the cell colours can be identified it is simple to incorporate the results into a regular function. Before you do all of this however, please read the final section on this page about conditional formatting.

Two alternative functions return a numeric value which is unique to specific colors. You can interchange elements of these examples to build something that best suits your needs.


Function BGCol(MRow As Integer, MCol As Integer) As Long
BGCol = Cells(MRow, MCol).Interior.ColorIndex
End Function
This returns a value dependent upon the background colour of a particular cell. The row number and column numbers are each entered as numbers so that (3,5) would represent cell E3.
Function FGCol(MyRef As Range) As Long
FGCol = MyRef.Font.Color
End Function
This returns a value dependent upon the font colour of a particular cell. An argument giving the cell reference is required in the normal format e.g. B2.

examples of font and background (interior) color values
[2] If the function is required only in the current spreadsheet or will be shared insert a module in your current file. If it is needed in various spreadsheets running on your own PC, use the personal.xls file.

The two functions are applied in the example (left) to illustrate the color values. Note that the ColorIndex property and the Color property are completely different and return different values for the same colour. Take your pick which one you want to use - I have used a different one in each function simply to highlight the choice.

To use one of the new functions, either type it directly in a cell [e.g. =FGcol(A9) ] or use the paste function button and select it from the list within the 'User Defined' category. Format a series of cells with the colours you wish to identify and then note the color (or ColorIndex) values for each one.

Would you like to learn more?

click link to find out about this book

Excel for dummies 2010

Note that these simple functions only report on the value of a single cell. There are examples of more complex functions on other websites but they require an entire range to be a particular colour. There is an additional problem - these functions will not automatically refresh if a cell colour changes. The user must force a complete recalculation by selecting <ctrl>+<alt>+<f9>. A normal <f9> recalculation is not sufficient.

The code can be modified to do something special with the program memory. If this is done the <f9> button alone will be sufficient. I do not know the performance implications of this extra line of code:

Function BGColR(MyRef As Range) As Long
Application.Volatile (True)
BGColR = MyRef.Interior.Color
End Function

These functions do not work on colours generated by conditional formatting. They require you to have hand painted each cell.

Doing sums based on color values

If the calculation problems have not put you off the prospect of using this function, the following technique can SUM the values. This example shows a series of scores of different classes. The cells in column D have been coloured green for good scores (>40) and red for bad scores (<20).

Summing and counting values depending on cell colors

The formulas in the summary table (C17:F20) are actually dependent on the ColorIndex values in column F.

[F9] =bGColr(D9)
[D18] =SUMPRODUCT(($D$9:$D$15)*($F$9:$F$15=4))
[E18] =SUMPRODUCT(1*($F$9:$F$15=4))

The SUMPRODUCT formulas look for ColorIndex values of 4 (green), 44 (orange) or 3 (red). If the cell colours change, you must recalculate the worksheet to revise the values in column F. (Note values may be different depending on your local setup).

Conditional Formatting

A much better solution is to paint the cells using Conditional Formatting - this can change the colouring automatically depending on the value each cell contains. The VBA functions described above will not work with this process. It is however possible to use other formulas to mimic the same conditions which are applied to the formatting.

excel colors

Add three conditional formatting rules to the range D9:D15 (which must have been reset to background color 'No Fill') to replicate the effects shown in the example above. Then in a separate range create a small LookUp table to give a value to each range of scores. The effect of the LookUps will be that 0-19 are labelled 1, 20-39 are labelled 2, 40 and over are labelled 3.

The previous BGCOLR() formulas in column F are replaced with VLOOKUP formulas which check the scores against the new lookup table.

[F9] =VLOOKUP(D9, $J$6:$K$8, 2)

Looking up the value of 25 in cell F9 will return a value of '2'. The SumProduct calculations in cells D18:E20 can then be slightly modified to read the alternative lookup results of 1, 2, or 3.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: excel-color-calcs.htm meadinkent.co.uk 2014 Last updated Feb14 CMIDX S3 P3 Y