MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summing by colour function


Posted by Richard on December 13, 2001 3:41 AM

i have a function which picks up the sum of a series based on colour of the cell...unfortunately my data is in the series:

date inv no. amount date inv no. amount Total O/S
5-march 452 67.52 15-3 462 87.53 155.05

how can i get the function to ignore the invoice number and dates values when summing.

the O/s column is for invoices that have not been paid and i change the colour of the amounts once they have been paid.

They need to be on the same line as they are for one order invoiced over a period of time

thanks richard


Posted by Dan on December 13, 2001 7:09 AM

Post your current function so that we can take a look and fix.

Posted by Richard on December 14, 2001 12:36 AM

it was from www.ozgrid.com

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