I have a user defined function that will average values in a list only if the
font color is black or automatic. I am trying to figure out how to have excel
trigger the function to update once the font color of one of the cells has
been changed. I was using a Worksheet_SelectionChange event handler
to have the sheet recalculate after the font color had been changed and the
user selects a new cell. This works well but requires each sheet to have the
SelectionChange code (not a big problem). The bigger problem is that
whenever there is any selection change it triggers the macro and therefore
the undo functionality is lost and if you try to copy then select a different cell
and paste the clipboard has lost what was copied to it.

Excel 2000 on Windows XP

code being used:

Function AvgIfBlack(myRange As Range) As Double
Application.Volatile
Dim myCell As Range, Sum As Double, SumCount As Integer
For Each myCell In myRange
If myCell.Font.ColorIndex = 1 Or myCell.Font.ColorIndex = -4105 Then
Sum = Sum + myCell.Value
SumCount = SumCount + 1
End If
Next myCell
AvgIfBlack = Sum / SumCount
End Function


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub


Any help or suggestions for a better solution to have the sheet recalculate would be apprecaited.

TIA