VAB Font Colors

[JamesPW]

An interesting thing I came across and I was wondering if anybody else has seen it and is there a work-around. Here's my code

Function SumByColor(CellColor As Range, rRange As Range)
Application.volatile
Dim cSum As Double
Dim ColIndex As Integer
Dim TempNumber As String

ColIndex = CellColor.Font.ColorIndex
For Each cl In rRange
If cl.Font.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

I have a column of numbers. Some have FontColor = Orange, some have fontColor = Black. I want to sum everything where fontColor=black.
The reference cell/font (ColIndex) is Black
SumByColor will sum the black cells but if I change an orange cell black or a black cell orange SumByColor does not update.
It does recalculate if you change the value of any cell on the sheet.

Can anybody confirm this and is there a way I can get SumByColor to update by changing the font color?

 

[jasonb75]

Can anybody confirm this and is there a way I can get SumByColor to update by changing the font color?

It needs to be an action that will trigger recalculation. Changing the font colour alone will not do it.

You can force recalculation by pressing the f9 key instead of changing a cell value.

 

[Sulprobil]

With CTRL + ALT + F9 you can enforce recalculation.
F9 will not suffice, I am afraid.

 

[jasonb75]

F9 will not suffice, I am afraid.

F9 will suffice with volatile functions.

 

[Fluff]

F9 works for me with that code.

 

[Sulprobil]

F9 will suffice with volatile functions.

Apologies - I did not see the command Application.Volatile.
CTRL + ALT + F9 should work without this command, though.