MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

Push Alt+Q to return to Excel.

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

OzGrid Business Applications

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


OzGrid Business Applications

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


OzGrid Business Applications

Posted by Dave Freeman on February 15, 2001 8:26 AM

Dave, thanks again.

It now works, ie shows a 0 in the cell but doenst ever change ?

Posted by Dave Freeman on February 15, 2001 8:26 AM

Dave, thanks again.

It now works, ie shows a 0 in the cell but doenst ever change ?