| | Feedback | Conditional Formatting | | ||
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 |
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 |
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. |  |
|
 |
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.
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).
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).
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.
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))
 |
Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
| file: excel-color-calcs.htm | © meadinkent.co.uk 2016 | Last updated Apr15 | CMIDX S3 P3 Y |