Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
With Target
If .Value < "*KEY_ERR" Then
.Offset(0, 1).Value = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
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)


"Mitch Powell" <Mitch wrote in message
...
I want to write a VBA function to do the following:

Function resides in cell B1 and references the value of A1 (A1 references
other parts of the workbook).

Value of A1 is 5, so value of B1 is also 5.

User changes some input data and A1 changes to 17, so B1 is now 17.

User makes additional changes resulting in A1 changing to *KEY_ERR.

VBA function in B1 tests for this condition and returns the last good
value
in A1 (17).

Any suggesstions?