SUMIF's with Shading or Fill

[Revelations]

Is there a way to sum numbers only if they are shaded in a certain color?

Thank you.

[Al Chara]

Do you have a problem with using a macro?

[Revelations]

Thanks for responding. I am a novice; but I have recorded a few before. Never really used visual basic.

I am open.

[Al Chara]

Try the following:

Sub SumShaded()
Dim total As Long
For Each cell In Selection
If cell.Interior.ColorIndex <> xlNone Then _
total = total + cell.Value
Next
MsgBox "The total of the cells is " & total
End Sub

Repost if you need editting.

[Revelations]

Wow, thanks that was great. Not to be picky but if I could get the total to show cents as well and dollars and perhaps fix in on a certain range instead of having to highlight a range first.

Thanks again.

[Al Chara]

On 2002-05-23 13:43, Revelations wrote:
Wow, thanks that was great. Not to be picky but if I could get the total to show cents as well and dollars and perhaps fix in on a certain range instead of having to highlight a range first.

Thanks again.

A static range is no problem. Tell me what you want it to be. I'm not sure I understood your dollars and cents request. Do you want the format to be $##.##?

[Al Chara]

Anyway, try the following:

Sub SumShaded()
Dim total As Currency
For Each cell In Range("a1:a5")
If cell.Interior.ColorIndex <> xlNone Then _
total = total + cell.Value
Next
MsgBox "The total of the cells is " & FormatCurrency(total)
End Sub

_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-23 13:52 ]

[Revelations]

Thanks again.

It currently reads the total is 499 versus 498.47

As for range: E2:E250

Thanks

[Revelations]

That last one worked. I really appreciate your help. That will save me a lot of time.