| Feedback | Conditional Formatting |

Calculating values of coloured cells

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

This is a peculiar topic but is one that has been requested by several people. The aim 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 useful formula. Before you do all of this however, please read the final section on this page about conditional formatting.

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

Function BGCol(MyRef as Range) As Long
BGCol = MyRef.Interior.ColorIndex
End Function

This returns a value dependent upon the background colour of a particular cell. An argument supplying the cell reference is required e.g. $B$2.
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.

The two functions are applied in these examples to illustrate the color values of the text and the cell background colour. Note that the ColorIndex property and the Color property are completely different and return different values for the same colour. Excel's ColorIndex numbers are an internal system of values with only 56 values and it is possible that different colours may share the same index number. The Color numbers relate to the RGB codes and should be unique to any colour. Take your pick which one you want to use - I have used a different one in each function simply to highlight the choice. colorindex values
Color (RGB) values
[2] If the function is only required in the current spreadsheet, insert a module in your current file. If it is needed in various spreadsheets running on your own PC, use the personal.xlsb file.

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.

If you wish to display a Color number as a hex value, the following function will adapt the value. =RIGHT("000000" & DEC2HEX(FGCol(B4)),6)

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 colour 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 colours set using conditional formatting. However it is possible to use other formulas which are based on the same conditions that have been applied to the formatting.

conditional formatting with three rules

Add three conditional formatting rules to the range D9:D15 (which no longer require any hand-painted background colours). These rules can be specified by banding values saved in a range of cells.

Summarising the table data to add up or count the values in each banding group can be achieved with functions such as SUMPRODUCT() or SUMIF(). e.g. to sum the values between 20 and 39 (coloured orange)  =SUMPRODUCT((D9:D15>=20)*(D9:D15<=39)*(D9:D15))

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 2016 Last updated Apr15 CMIDX S3 P3 Y