Log previous value in seperate worksheet cell

I have developed something like this, I am driving right now. As soon as I get home I will post the code

You could store the previous value in a cell comment. I can provide code if you want

Sounds great, thank you!

@Roy_Cox: would the values stored in comments be accessible to my code? I need the delta btw the current value and the previous value in some other code.

put this code into the worksheet module.  not in a simple module.

the below code will work for any change in any cell in entire worksheet

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then
        preValue = "a blank"
    Else: preValue = Target.Value
    End If
End Sub

Select allOpen in new window

Here's an example
Example File

the below code is for specific range

change the range as per your need $C$4:$H$15 to your specified range

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$C$4:$H$15")) Is Nothing Then Exit Sub
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$C$4:$H$15")) Is Nothing Then Exit Sub
    If Target = "" Then
        preValue = "a blank"
    Else: preValue = Target.Value
    End If
End Sub

Select allOpen in new window

if you do not know how to put this code and where.  see attached file
EE.xlsm

and this one is my favorite that keeps all the records. see attached
EE.xlsm

Roy,
i checked your file. it does not add the track changes of cell values if it is other than text.   it only works with text but if i change a cell value to number then it does not work.

I have to go out for an hour and I'll check when I get back

Easier than I thought. Use this code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next

    Dim Proceed As Long
    Proceed = MsgBox("Do you want to add the text of your cells to each comment?    " & vbCrLf & _
                     "This will delete any existing comment text", vbQuestion + vbYesNo)
    If Not Proceed = vbYes Then Exit Sub
    With Target
        If .Comment Is Nothing Then
            .AddComment
            .Comment.Text CStr(.Value)
        Else: .Comment.Text Text:=CStr(.Value)
        End If
    End With
    On Error GoTo 0
End Sub

Select allOpen in new window

If you want code to write the saved value back to the cell let me know

Hi guys,

Thanks - Almost what i was looking for!

However, the cell(s) i'm trying to log historical values for contains a formula. I've attached a sheet that hopefully explains better what i'm trying to accomplish.

Thanks & regards
EE-Example.xlsx

Decisionfocus,

What you are looking forward for it's a bit tricky to do since in excel you don't have any before-calculation event..So now in order to achieve this i'm setting up your calculations to manual when you open your workbook so that your values doesn't get updated now once you enter a new value in the range you defined in your example i pick up values from your range and move it over to your desired range force the calculations and again turn it back to manual that way it will do what you are looking for..

In additional when you close this excel file your calculations are back to automatic again..

Refer to enclosed file for working example as it does what you are looking for...

Saurabh...
EE-Example.xlsm

Saurabh, that's extremely useful. It does exactly what i want!

It seems, however, that i hadn't properly thought my needs through. I need this to only execute when another sub in the same sheet is executed, i.e. to call it from that sub:

Public Sub UpdateScenario(ByVal Target As Range, Cancel As Boolean)

      call Worksheet_Change....

However, the target here is different so i'm a bit unsure of how to do this. Any ideas would be very appreciated!

Br Jonas

There is no code in your example workbook. Please attach an example with the code in.

I adapted Saurabhs code and made it work. Thanks for your help!

Br Jonas