Excel 2010 Macro Sum Cells Based on Color
Sum Cells Based on Interior Color
Say you have created a list of how much each of your clients owes and you want to sum just those cells you have colored to indicate clients who are 30 days past due from this list.
The arguments are:
- CellColor—The address of a cell with the target color
- SumRange—The range of cells to be searched
Function SumByColor(CellColor As Range, SumRange As Range)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex •get the target color
For Each myCell In SumRange •look at each cell in the designated range
•if the cell color matches the target color
If myCell.Interior.ColorIndex = iCol Then
•add the value in the cell to the total
myTotal = WorksheetFunction.Sum(myCell) + myTotal
End If
Next myCell
SumByColor = myTotal
End Function