How can I calculate using a background/fill colour ?

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

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 !!

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 !!

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

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

Dave, thanks again.

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

Dave, thanks again.

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