Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home





Sumif on cells color

Posted by Sam Brown on May 25, 2001 4:15 AM
I'm sure this can be done but i'm not sure how.

I got cells with various colors/numbers and I would like to add up those which are red, etc.

thanks

Sam


Check out our Excel VBA Resources

Re: Sumif on cells color

Posted by Dave Hawley on May 25, 2001 4:45 AM

Hi Sam

I have a custom function that I have written for this. To use push Alt+F11 and go to Insert>Module and paste in this code:


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

Then push Alt+Q to return to Excel and Save.

Now in any cell put:


=SumColor(A10,A1:A20)

Where A10 interior Color is Red (or any other color) and A1:A20 is the range you want to Sum.


Dave

OzGrid Business Applications


Re: Sumif on cells color

Posted by CogswelCogs on May 29, 2001 7:04 AM
You can write a function using this for ex.
I dont think you can do it outside of VBA

If Cells(HC, xcol).Interior.ColorIndex = 8 Then




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.