# How can I calculate using a background/fill colour ?

Posted by Dave freeman on February 15, 2001 3:22 AM

Hi,

Sorry not a huge expert in Excel !

I am creating a holiday planner where the user will highlight the dates for holiday.

I would like Excel to then count and total the amount of green cells, the amount of red cells etc. in a particular row.

Any tips would be greatly appreciated !!!

Posted by Dave Hawley on February 15, 2001 6:31 AM

Hi Dave

You will need a custom function to do this, so i havbe written one for you. Tpo use it push Alt+F11 and paste in this code:

Function SUMBYCOLOUR(CellColour As Range, SumRange As Range)
Dim cell As Range
Dim SumColour As Integer
Dim MySum

SumColour = CellColour.Interior.ColorIndex

For Each cell In SumRange
If cell.Interior.ColorIndex = SumColour Then
MySum = cell + MySum
End If
Next cell
SUMBYCOLOUR = MySum
End Function

Now in any cell put:

=SUMBYCOLOUR(C6,C5:C50)

Where C6 contains the background colour. C5:C50 is the range of cells to colour.

As this uses a Loop, I would suggest you dont try to sum a range > 2000 rows. And don't try to sum an entire Column. It will work OK but will slow down recalculation.

Dave

Posted by Dave Freeman on February 15, 2001 7:06 AM

Hi,

Thanks a lot for your help, very much appreciated.

However, I cant get it to work.

When I put in the =SUMBYCOLOUR..... into a cell it doenst return a figure, only #NAME?

Apologies if I am being stupid !!

Posted by Dave Freeman on February 15, 2001 7:06 AM

Hi,

Thanks a lot for your help, very much appreciated.

However, I cant get it to work.

When I put in the =SUMBYCOLOUR..... into a cell it doenst return a figure, only #NAME?

Apologies if I am being stupid !!

Posted by Dave Hawley on February 15, 2001 7:44 AM

Sorry Dave, my fault i missed a vital bit of the intructions.

To use it push Alt+F11, Then go to Insert>Module and paste in the code:

Now it will work.

Dave

Posted by Dave Hawley on February 15, 2001 7:44 AM

Sorry Dave, my fault i missed a vital bit of the intructions.

To use it push Alt+F11, Then go to Insert>Module and paste in the code:

Now it will work.

Dave