The following function calculates the total value of all the cells in a particular range that are a particular color:

Unfortunately, there is no SUMIF Function to sum based on cell color. If you want to sum by color, you will need to create a function within VBA.
To use this code: Open the Visual Basic Editor (Alt + F11), Insert a new module (Insert > Module) and copy & paste the desired code into the module.

Function to Sum by Color

This is in effect “sum by color” – so if you know Excel’s 56 color palatte and you know for example that color 4 is light green then the following call:


will sum the values for all the cells in the range A1:P20 that are light green in color.

To make using the function easier, the following subroutine will work out the total value for each of excel’s 56 colors. It also gives the entire palatte so that it is easy to see the index number for each color.

The subroutine is invoked on sheet 1 and looks at the range

To download the XLS file, please click here

VBA Coding Made Easy

Stop searching for VBA code online. Download our free add-in to quickly insert any of the sample code found on our site into the Visual Basic Editor.

alt text

Stop searching for VBA code online. Try the Code VBA Add-in…

Free Download!

<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues: