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
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
If you want to use this code in your VBA tool, then follow below steps:
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
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
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
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
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.
VBA Code to Convert MM.DD.YYYY To DD.MMM.YYYY in Excel 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 CODE TO HIDE MENU RIBBON IN MS ACCESS In MS Access, there are multiple ways to protect your code or tool from un-authorized access and edits. Some developers prefers to hide MS Access ribbons…
How to Find Duplicate Files In excel using VBA? Yesterday I was working on my computer and cleaning the drives to make some more space. I was surprised to see so many files saved at…
Lock Cells to avoid editing, Hide Formulas Sometimes you create amazing projects, dashboards however people can make mistakes and edit the calculations, formats what you have made. Still you can protect the sheets, workbooks and…