Triggering UDF
 

I have a user defined function that will average values in a list onl
if the
font color is black or automatic. I am trying to figure out how t
have excel
trigger the function to update once the font color of one of the cell
has
been changed. I was using a Worksheet_SelectionChange event handler
to have the sheet recalculate after the font color had been changed an
the
user selects a new cell. This works well but requires each sheet t
have the
SelectionChange code (not a big problem). The bigger problem is that
whenever there is any selection change it triggers the macro an
therefore
the undo functionality is lost and if you try to copy then select
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 = -410
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 shee
recalculate would be apprecaited.

TI

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=55714