Sum by color using VBA in Microsoft Excel

In this article, we will create a custom function to calculate the sum of numbers in cells in the range having defined color.

For this example, sample data consists of sales team data. Sales team data contains details related to number of items sold. It contains information of related to product which is available in three colors Grey, Red and Green. We want to find the sum of item sold by product color.

To calculate the sum by color, we have created custom function “SumByColor”. This function take two range parameter as input. First range parameter define the interior color of the cell and second range parameter define the sample range.

Code explanation

Application.Volatile is used because it will recalculate every time a cell value is changed in the workbook.

DefinedColorRange.Interior.ColorIndex

Above code is used to get the interior color of the defined range.

Please follow below for the code

Option Explicit
Function SumByColor(DefinedColorRange As Range, SumRange As Range)

Application.Volatile

'Declaring variables
Dim ICol As Integer
Dim GCell As Range

'Getting the interior color of the cell
ICol = DefinedColorRange.Interior.ColorIndex

'Looping through the defined range
For Each GCell In SumRange
    
    If ICol = GCell.Interior.ColorIndex Then
        'Getting the sum of matching colored cell
        SumByColor = SumByColor + GCell.Value
    End If
Next GCell

End Function

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com