Adding cells that are a certain colour

Posted by Terry Bowen on January 16, 2001 2:53 AM

Hello Everyone
I have a spreadsheet that has some of the cells in a different colour(Text colour). What I want to do is if these cells are coloured Red for example I want to add them together, but I do not want to include any other colour, eg if there are 3 red cells and 2 green cells I only want to add the red cells together. Can this be done?

Thanks

Terry

Posted by Mark W. on January 16, 2001 6:35 AM

Excel isn't designed to use color in this way. Of
course you probably can find someone to write some
VBA to achieve your objectives, but it's a bad
practice. Care to explain more about the design
of your worksheet, the meaning of the colors, and
your intent?

Posted by Dave Hawley on January 16, 2001 10:19 PM

Hi Terry

Here is a User Difined Function that will add all cells of the same font color.

Function ColorSum(ColorCell As Range, SumRange As Range) As Variant
Dim Cell As Range
For Each Cell In SumRange
If Cell.Font.ColorIndex = ColorCell.Font.ColorIndex Then
ColorSum = Cell + ColorSum
End If
Next
ColorSum = ColorSum
End Function

to use it Push Alt+F11 then go to Insert>Module and paste it in. Now push Alt+Q and save.

Now in any cell put: =ColorSum(C2,C2:C10)

Where C2 contains the font color to sum. And C2:C10 contains the cells to sum of the same font color as C2.

It maybe a bit slow on large ranges, dont be tempted to select an entire Column either.

Hope this helps
Dave

OzGrid Business Applications

Posted by Terry Bowen on January 17, 2001 9:08 AM

Just like to say that it works and thanks for your help