Record Cell Value Change

[jk64]

I need a function that records the number of times a cell in a worksheet has a value change.

For example if cell J5 has the value 6 entered and that cell value is changed to 15 this would be recorded as 1 in the cell using the function. If that Cell value is then changed to 11, the cell change would be recorded as 2.
For clarity: I want to be able to put in, say, cell L5 =Cell_Value_Cahnge(J5), and have cell L5 automatically updated as the values in cell J5 change.

I'm not sure if there is a way to do this using the IF() function, but i tried and it wasnt working. I searched around teh web for help and tried taking a stab at VBA, but to no avail. I'm really new to all this code stuff so help would be very much aprreciated.

 

[VoG]

This is similar to what you are asking. Maybe you can adapt it.

http://www.mrexcel.com/forum/showthread.php?t=381604

 

[jk64]

Thanks Peter for your quick reply.

I searched though the thread and tried a couple of things, namely the code oin post 67.

Unfortunatly I am completly new to this VBA stuff, (I only discovered how to get to VBA mode on monday,) so I really couldn't get anywhere with it. Maybe I was playing with the wrong code, or maybe I was changing the wrong things, I have no clue.

Further help of any kind would be greatly appreciated.

 

[VoG]

Try this. First create a sheet called Log. Then press ALT + F11 to open the Visual Basic Editor, in the Project window double click ThisWorkbook and paste into the white space on the right

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LR As Long, X As Variant
If Sh.Name = "Log" Then Exit Sub
If Target.HasFormula Then Exit Sub
X = Target.Value
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    If Target.Count = 1 Then .Undo
End With
With Sheets("Log")
    .Unprotect Password:="pw"
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Now
    .Range("B" & LR + 1).Value = Sh.Name
    .Range("C" & LR + 1).NumberFormat = "@"
    .Range("C" & LR + 1).Value = Target.Address(False, False)
    .Range("D" & LR + 1).Value = Target.Value
    .Range("E" & LR + 1).Value = X
    .Range("F" & LR + 1).Value = Environ("username")
    .Protect Password:="pw"
End With
If Target.Count = 1 Then Target.Value = X
On Error Resume Next
Target.Offset(, 1).Select
On Error GoTo 0
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

Press ALT + Q to close the code window. Then try changing a few cells here and there and observe the entries in the Log sheet.

 

[jk64]

Thanks again for the help Peter.

I did exactly what you said, and it did a fantastic job of recording every change to the worksheet. Unfortunatly that is not really what i wanted.

Let me try to clarify: I need something that i can type into a cell like a function, select the specific cell i want to follow, and the out be a running count (ie: 1,2,3...) of teh number of changes the cell underwent.

For example, if i want to track the number of changes to cell A1 in cell B1, I want to be able to type in: B1=function_name(A1). Then if I press enter, the output would be the running count of the number of changes to A1. So if A1 was initially 5, the I chagned it to 43, B1 would read 1. Then if I change A1 to 23, B1 would read 2.

I hope that clarifies my question, and I hope you can help.

 

[VoG]

OK, this is specific to changes in A1 with the changes count recorded in B1. It assumes that you are changing A1 manually. Right click the sheet tab and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Static OldVal As Variant
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
    If Target.Value <> OldVal Then
        Target.Offset(, 1).Value = Target.Offset(, 1).Value + 1
        OldVal = Target.Value
    End If
    Application.EnableEvents = True
End If
End Sub

 

[jk64]

That worked beautifully, Peter. Thank you.

Curious,Since I am probably going to change the tracked cell to something that is not manually changed, how would that change the code?

 

[VoG]

f A1 contains a formula try

Private Sub Worksheet_Calculate()
Static OldVal As Variant
With Range("A1")
    Application.EnableEvents = False
    If .Value <> OldVal Then
        .Offset(, 1).Value = Target.Offset(, 1).Value + 1
        .Value
    End If
    Application.EnableEvents = True
End If
End Sub

 

[jk64]

And what if its something like Stock prices, where it automatically updates, like a live feed?

Sorry about all of the follow ups. I just want to be sure to have exactly what I need when i put my final spreadsheet together.

Thanks for all the help Peter.

 

[VoG]

For a live feed this is probably better http://www.mrexcel.com/forum/showpost.php?p=2746419&postcount=6

Please test.