EXCEL VBA – Sum by Color


The text that follows is owned by the site above referred.

Here is only a small part of the article, for more please follow the link

SOURCE: http://www.automateexcel.com/vba/sum-by-color

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s