user defined function not updating

[SmithyJD]

I have a custom fucntion which sums the values in a range depending on the background colour of the cell it works fine however it does not auto update when the colours change and the user has to manually recalculate the sheet by pressing f9 any ideas?

the code is below

Function SumColor(rColor As Range, rSumRange As Range)
'Written by Ozgrid Business Applications
'

http://www.ozgrid.com

Application.Volatile True
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

Thanks in Hope

JD

 

[combo]

There's nothing to do.
This function will recalculate when you change contents of any cell, excel will recalculate it and its dependants, and next all UDFs that have Application.Volatile. Unfortunately, formatting changes and changes in drawing layer are not a good reason for excel to calculate something.

So you have either to press F9 or wait till any cell contents changes.

combo

 

[xlbo]

which is one of the best reasons to never try to do calculations based on a colour...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SmithyJD]

Trouble is Colour is one of the few things senior managers actually understand.

F9 it is thanks

 

[Gammachaser]

What causes the color change? If it is Conditional Formatting, then what causes the change that changes the condition?

 

[SkipVought]

Really not a good idea to change a color as a PROCESS step.

much better to enter a value that results in a color change as a PROCESS step.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SmithyJD]

The spreadsheet in question is a graphical representation (very simplistic) of future sales by week and the colours are changed by the user as they are updated form potential to firm order, to received hence some of it is real guess work/gut feel. Yes we could add a flag for status and sumif depending on value but to be honest the colour option fits in this case I feel.

 

[SkipVought]

Then you need a BUTTON CLICK event to do the calculation. Put a button on your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SmithyJD]

Already done