sum by color

[salth2o]

I'm using the following code which I know works...but for some reason it's NOT working in my spreadsheet. It's adding the entire column regardless of the color I've specified in the range cell. Any thoughts?

Function SumColor(rColor As Range, rSumRange As Range)
'Written by Ozgrid Business Applications
'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

 

[schielrn]

Are your cells being colored by conditional formatting or just a fill color?

 

[jdc]

Have to tried refreshing the formula and see if that makes any difference, sometimes it doesn't calculate automatically for a reason unknown to myself.

 

[schielrn]

You could try a complete re-calc by using Control+Alt+F9. IF that fixes it, it may be what jdc was referring to and then in the function you can add:

Application.Volatile = True

This will make it volatile and calculate with every change int he workbook.

Hope that helps.

 

[salth2o]

I've refreshed the calculating field. Thanks for that tip though! Still doesn't change the outcome. Cells are formatted green by text, not fill.

 

[schielrn]

This function is looking at fill color, not text color? Do you need text color?

If so try:

Function SumColor(rColor As Range, rSumRange As Range)
'Written by Ozgrid Business Applications
'www.ozgrid.com
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Font.ColorIndex

For Each rCell In rSumRange
If rCell.Font.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell

SumColor = vResult
End Function

This is checking font color against a cell in the formula signifying font color, but maybe you want to use a cells fill color to sum on?

 

[salth2o]

That worked but my cell that is using the "green" to tell it which color to sum...I had to change the font color of that cell to green instead of just the word green.

Still learning........

Thanks!!!!

 

[schielrn]

Which way or how do you want it? It isn't as easy to use the word, but can be done if you would like it that way?

 

[salth2o]

Oh I just colored my word green and it worked. No problem. Thanks again!!!