No that is effectively the same thing. Try Worksheet_Calculate

Private Sub Worksheet_Calculate()
If Me.Range("A1").Value 10 Then
'do your stuff
End If
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Macroman" wrote in message
...
Thanks , is it therefore possible then to call a subroutine from within a
funcion and have the subroutine do the work.


"Niek Otten" wrote in message
...
Hi Macroman,

There is no way you can change anything in your spreadsheet (values,
formats, etc) from within a function.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Macroman" wrote in message
...
I am writing a simple function which asks the user for a range of cells.

I would like the range of cells analysed and replaced with new values
but
I am unable to write back to the spreadsheet.
I can create a Sub (macro) to do this but I prefer a function then I
can
add the function to my UDF AddIns.
The code as follows
-----------
Function Grab(Grab_Range As Range)

intCellLoc = ActiveCell.Address
varSheetName = ActiveSheet.Name
intRows = Grab_Range.Rows.Count
intColumns = Grab_Range.Columns.Count

intRepRow = Grab_Range.Cells(1, 1).Row
intRepCol = Grab_Range.Cells(1, 1).Column

If intColumns < 1 Then ErrMsg = "#Column Err#": GoTo Finished

########It is this bit I cant get working#############
For i = 1 To intRows
Sheets(varSheetName).Cells(intRepRow+(i-1) , intRepCol).Value = "Test"
Next
######## Any Ideas ? ############
Finished:
If ErrMsg = "" Then ErrMsg = "Finished"
Grab = ErrMsg
End Function

--------------
thanks

Macroman