Recalculate user function

[gregpet]

I have a list of values and want to add the ones up that I change the font color on. I am having trouble with the user function on recalculating. The only time it works is when I select the formula in the cell and hit enter. Here's what I have

Function SumFontColor(rng As Range, iColor As Integer) As Double
Dim rngAct As Range
Dim dAdd As Double
For Each rngAct In rng.Cells
If IsNumeric(rngAct.Value) Then
If rngAct.Font.ColorIndex = iColor Then
dAdd = dAdd + rngAct.Value
End If
End If
Next rngAct
SumFontColor = dAdd
End Function

as the user function in cell c75

I tried this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells("c75").Calculate
End Sub
on the worksheet module but it does not seem to help.

 

[Andrew Poulsom]

Have a read of this:

http://www.cpearson.com/excel/colors.htm

Note the:

Application.Volatile True

in Chip's functions.

 

[gregpet]

That does not seem to help. The sheet (and function) recalculate only when a value changes. I cannot seem to force calculation in any way.

I added this to change a value, causing it to recalculate.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("c76") = 0 Then
Range("c76").Value = 10
Else
Range("c76").Value = 0
End If

not pretty but it works for now.