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
NoteIt 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
From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free VBA tool from...
In different parts of the world, there are different languages spoken and written. With this, a VBA programmer also faces language related issues while writing a VBA program. Here is a common date related problem…
Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you here.
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 multiple users. Still people prefer to pull and see the…
Watch Excel Tutorial Video - VBA Code to Find Duplicate Files Yesterday I was working on my computer and cleaning the drives to make some more space. I was surprised to see so many files...
WORKDAY.INTL function is an advanced version of WORKDAY function with additional advantage of “Custom weekend options” For Example, with WORKDAY function weekends are treated as “Saturday and Sundays” however if you need the weekend to…
One Comment