Recalculate user function

gregpet

Board Regular
Joined
Mar 6, 2006
Messages
53
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

gregpet

Board Regular
Joined
Mar 6, 2006
Messages
53
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.
 

Forum statistics

Threads
1,141,049
Messages
5,703,932
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top