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
VBA Code to Count Color Cells With Conditional Formatting Have you ever got into situation in office where you need to count the cells with specific color in conditional formatted Excel sheet? If yes then…
VBA to Browse Outlook Folder Outlook is most commonly used emailing application used in the world. Many people spend their entire day on Outlook applications to read and respond to emails. To automate certain rule-based…
VBA Code to list Files in Folder To work on multiple files through VBA programming, you need to have VBA code that can list files in a folder. Here is a simple code for you,…
What is Pareto Chart? Pareto principle was introduced by Italian Economist Vilfredo Pareto. He stated that 80% of the effects are caused by 20% of the causes. So if we closely monitor and solve 20% of…
How to use VBA to open Workbook in Excel? There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a…
VBA Code to Browse a Folder Quite often a VBA developer requires code to browse a folder. This is mainly for saving the output file or reading the input file(s). Below is the VBA code…
Hi,
How to Count (D71) CONDITIONALLY FORMATTED RED COLOR CELL only (D40:D70)?
Pls guide…
Thanks
If you want to count from single cell then formula will be =SumByColor(D71,256,0,0)