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:
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
