Question on Custom Sum Function

[Ray Batig]

Greetings,

I have used the Function below to sum up cells in a column which contain red
font numbers. I find that it works very well, however, it does not
automatically update. I found that if I change the font of an existing cell
entry to red and then enter a number in the same column and press return,
the cell containing the SumRed function does update, however, none of the
other columns update. I saw that Chip Pierson commented on a similar
function on his wonderful site and said that if I used Alt+F9, the cell
containing the function would update. This doesn't seem work at all. Is
there a work around or am I relegated to writing a macro to enter a value in
a cell in each column and then use the same macro to erase the cell entry?

Thanks in advance for your help!

Ray

Function SumRed(SelectedCells As Range)
' Adds the values of the cells where the font color is red(3).
Dim Cell As Object
Dim x As Double
x = 0
For Each Cell In SelectedCells
If Cell.Font.ColorIndex = 3 Then
x = x + Cell.Value
End If
Next Cell
SumRed = x
End Function

 

[Peter Beach]

Hi Ray,

Sadly changing the format of a cell doesn't tell XL that it needs to recalc
:-(

One solution is to declare the function as Volatile. To do this you need to
enter the line:

Application.Volatile

at the start of the function. This means the function will be called by XL
whenever it does a recalc of the spreadsheet. If you make this change your
spreadsheet will take longer to calculate, but it should update your
function. To trigger a recalc simply enter a random value in any old cell.

HTH

Peter Beach

 

[Ray Batig]

Hi Peter,

Thanks. I can't see a difference in the calc time since the machine is way
faster than I am!!

Ray