|| Feedback | Conditional Formatting ||
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
|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
|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.|
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?
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
BGColR = MyRef.Interior.Color
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.
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 this process. It is however possible to use other formulas to mimic the same conditions which are applied to the formatting.
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.
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: excel-color-calcs.htm||© meadinkent.co.uk 2014||Last updated Feb14||CMIDX S3 P3 Y|