Jemmeyson wrote:

Thank you for taking the time to reply to my query. I reviewed the code
and felt overwhelmed :-) I dont think this will work for me, its far to
complex. I require just one cell to be recorded before it changes to a
new value. Is there a simpler way to achieve this?

The problem is, how do you decide when to capture the old value? There are 3
user inputs, and your A4 will update every time one of them is changed. So
let's say the user has to change two values:

A1 1 - 4 - 4
A2 1 - 1 - 4
A3 1 - 1 - 1
A4 1 - 2 - 3

The "old value" you want to capture at the end of this is 1, but in the
simplest cases, the captured value will be 2, since Excel counts this as 2
changes.

I suppose you could do this in the ThisWorkbook object:

Private oldVal, vals

Private Sub Workbook_Open()
'get initial values, before changes can be made
oldVal = Sheet1.Range("A4").Value
vals = Sheet1.Range("A1:A3").Value
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.Saved = False Then Exit Sub
Dim L0
For L0 = 1 To 3
If vals(L0, 1) < Sheet1.Cells(L0, 1).Value Then
Sheet1.Range("A5").Value = oldVal
Me.Save
Exit For
End If
Next
End Sub

This only updates the "old value" cell upon closing the workbook (and saves
the workbook if needed), but *only* if the workbook has already been saved.
(In other words, if you decide to close out without saving changes, this
doesn't bother checking previous values.)

There are likely better ways to do this, and there are certainly other
possibilities, e.g. you could add a level of complexity and keep a running
record of old values, with the value from the previous save (or whatever)
highlighted somehow, or you could add code to watch for user changes to A5
and update oldVal with that, etc.

--
Whoa, there, Captain Angry, take a deep breath.