VBA Code to Sum Cells By Color

Recently some of our subscribers have requested us to share a VBA code that can sum the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to sum the cells with specific color and returns the total sum of the matching color cells

First VBA Code to Sum Cells by Color

Public Function SumByColor(rng As Range, Red As Long, Green As Long, Blue As Long) As Double

    'Variable declaration
    Dim dblSum As Double
    Dim rngCell As Range
    
    'Loop throught each cell in the range
    For Each rngCell In rng
        'Checking and sum color
        If rngCell.Interior.Color = RGB(Red, Green, Blue) Then
            If IsNumeric(rngCell.Value) = True Then
                dblSum = dblSum + rngCell.Value
            End If
        End If
    Next
    
    'Return the value
    SumByColor = dblSum
    
End Function

Second VBA Code to Sum Cells by Color

Public Function SumByColor(rng As Range, ColorCell As Range) As Double

    'Variable declaration
    Dim dblSum As Double
    Dim rngCell As Range
    
    'Loop throught each cell in the range
    For Each rngCell In rng
        'Checking and sum color
        If rngCell.Interior.Color = ColorCell.Interior.Color Then
            If IsNumeric(rngCell.Value) = True Then
                dblSum = dblSum + rngCell.Value
            End If
        End If
    Next
    
    'Return the value
    SumByColor = dblSum
    
End Function

Note It is worth to mention that both the above codes do not count the cells where colors are coming through conditional formatting.

How can I use this VBA code?

If you want to use this code in your VBA tool, then follow below steps:

Step 1: Open the Excel file in which you want to copy this code

Step 2: Press Alt+F11 to open VBA editor

Step 3: Insert a new module from Insert > Module menu

Step 4: Paste the code in the module

Step 5: Now you are ready to use this code as Excel Function/Formula

Step 6: Type the formula in the cell where you want to get the sum specific colored cells

First Code Example: =SumByColor(A2:A11,146, 208, 80)

The first parameter of the formula is the range from which you want to sum the specific color cells. The second, third and fourth parameters are RGB code of the color.

Second Code Example: =SumByColor(A2:A11,A3)

The first parameter of the formula is the range from which you want to sum the specific color cells. The second parameter is the cell from which you want to compare the color.

Note: In the first VBA code, we have used RGB color codes to find the matching cells and sum the values. You can find RGB codes of any color using following steps:

Step 1: Select the cell which contain the color you want to use

Step 2: Right click and select ‘Format Cells…’

Step 3: In the Format Cells dialog box go to ‘Fill’ tab and click on ‘More Colors…’

Step 4: That’s all, in the ‘Color’ dialog box, you can view the RGB (Red, Green, Blue) codes of the color

Thanks for reading the article, subscribe us to get more VBA tricks

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Recommended Articles

VBA Code to Send Outlook Emails

ByKailash Sharma

VBA Code to send Outlook Emails Sending bulk emails is a very common activity, there are many office activities that need a person to send bulk emails to single or multiple recipients. You also may…

Advanced Excel Techniques

How to mail merge from Excel to Word

BySumit Shrimal

This step-by-step guide will show you how to easily do mail merge in Word using an Excel sheet.
Mail Merge can save you a lot of time when you need to send letters or emails to many people. It helps you create personalized letters, emails, or labels in Word by combining the information from your Excel sheet. This guide gives an overview of the main steps and explains how to do a mail merge from Excel in an easy way

Productivity and Automation | Excel VBA and Macros

VBA Code to Export Access Recordset to Excel

ByKailash Sharma

How to Export Access Data to Excel using VBA Code? Creating a VBA tool in MS Access is always better as compare to MS Excel. MS Access provides better user interface and ability to handle…

Productivity and Automation

Duplicate Files Finder Tool

Byexcelsirji

Duplicate Files Finder Tool is an MS Excel based tool to identify duplicate files exist in your system or shared folder. The tool takes a base folder path as input and list down all duplicate files in the folder and sub-folders. It has capability to find duplicate files by comparing the names and their size.

Excel VBA and Macros

VBA Code to Add Items in ListBox Control in Userform

Byexcelsirji

VBA Code To Add Items In Listbox Control Using ListBox in Userform is very common. You can use ListBox.AddItem function to add items in the listbox.; however, it is little difficult to add items in…

2 Comments

Hi,

How to Count (D71) CONDITIONALLY FORMATTED RED COLOR CELL only (D40:D70)?

Pls guide…

Thanks

excelsirji says:

at

If you want to count from single cell then formula will be =SumByColor(D71,256,0,0)

Reply