Record Cell Value Change

**jk64** · 06-09-2011, 11:24 AM

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.

**tarquinious** · 06-12-2011, 11:16 AM

[QUOTE=jk64;2541506]I need a function that records the number of times a cell in a worksheet has a value change.
QUOTE]

If you put in the following VBA code into the appropriate worksheet you should get what you're after:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("J5") Then Range("K5") = Range("K5") + 1
End Sub

**jk64** · 06-15-2011, 02:34 PM

I found this worked well for things that are manually changed.

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

However, I am trying to track the number of cahnges a cell undergoes that has a live feed. for eample, I am getting an external refrence fed into the cell to give me quotes, and want to see how many times the cell value changes.