This could do with event disabling to stop unnecessary re-entry, error
handling, and testing for the target range

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Address = "$C$2" Then
If Target.Value Range("C1").Value Then
Range("c1").Value = Target.Value
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don Guillett" wrote in message
...
right click on the sheet tabview codecopy/paste this.modify ranges to
suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("c2") < Range("c1") Then Exit Sub
Range("c1").Value = Range("c2")
End Sub

--
Don Guillett
SalesAid Software

"icehot" wrote in message
...
Hi,

I have a formula in a cell currently, that is based on the value in
another cell. As the value in the other cell increases I want the
formula to be in effect and continue to calculate increasing values.

However, if the value in the other cell goes down, I no longer want
this formula to do anything, ie. the value it last calculated remains
in effect.

For your information this is to create a rising stop loss in an excel
portfolio. Hence the price may go up or down, but the stop loss
limit must only continue to rise.

Thanks,