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

VBA Code to Sum Cells by Color

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

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

VBA Code to Sum Cells by Color

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.

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

VBA Code to Sum Cells by Color

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

VBA Code to Sum Cells by Color

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

VBA Code to Sum Cells by Color

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

VBA Code to Sum Cells by Color

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

Leave a Reply

avatar
VBA Code to Count Cells by Color
VBA TRICKS
VBA Code to Count Cells by Color

Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.

FREE VBA UTILITIES
35
VBA Tool to Track Start and End Time (Time & Motion Tracker)

Here is one more wonderful free tool from ExcelSirJi.com which makes your life easy. Time & Motion Tracker helps you to track Start and End time of any type of transaction or activity. The good thing is, it is VBA based tool which helps you to protect from manual manipulation in the data by the user. It is also easy to use, just click on Start (shortcut: Ctrl+Shift+A) or Stop (Ctrl+Shft+S) buttons to record the time

VBA Code to Delete File
VBA TRICKS
VBA Code to Delete File

In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn