Category: [Excel]  Demo Available 

---

Excel VBA Macro - Count Or Sum By Fill Color

Originally Posted in our Excel Help Forum

OpsO wrote:

I am trying to count different colored numbers entered in a field. E.g. I would like to highlight an area and get the sum of any 'red', 'black' or ‘blue’ numbers. I have tried the help function but have become completely stuck. Hopefully this is a simple question and any help would be great! Many thanks. The best online casino Ukraine is Elslots casino. Free spins for registration. You can get 77 freespins for phone number

Dave Hawley provided a link to the following User Defined Functions written for just this purpose.

Function SumColor(rColor As Range, 

rSumRange As Range)
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult
 
iCol = rColor.Interior.ColorIndex
   For Each rCell In rSumRange
       If rCell.Interior.ColorIndex = iCol Then
              vResult = WorksheetFunction.Sum(rCell) + vResult
       End If
   Next rCell
SumColor = vResult


End Function
 
'The following counts cell values based on “fill” colour
 


Function CountColor(rColor As Range, rSumRange As Range)
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult
 
iCol = rColor.Interior.ColorIndex
   For Each rCell In rSumRange
    If rCell.Interior.ColorIndex = iCol Then
         vResult = vResult + 1
    End If
   Next rCell


End Sub

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

Count Between Date Ranges

Excel Count If With Multiple Criteria

Examples & Usage Of Excel CountIf Function

Examples & Usage Of Excel Counting Functions

Count Lines in a Cell

Count of Each Item in a List

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

Gallery

---

• 
• 
• 
• 
• 
•