How to detect amount of changes in cell?

Originally Posted by royUK

Perhaps storing as a Name would be best, then it should be less likely to be deleted accidentally

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellChanges As Long
    If Target.Address = "$A$1" Then
        On Error Resume Next
        CellChanges = Evaluate(ThisWorkbook.Names("USED").RefersTo)
        If CellChanges < 1 Then CellChanges = 1      'first used
        CellChanges = Evaluate(ThisWorkbook.Names("USED").RefersTo) + 1
        MsgBox CellChanges    '<- test delete
        ThisWorkbook.Names.Add Name:="USED", RefersTo:="=" & CellChanges
    End If
    On Error GoTo 0
End Sub

I take it this code changes the cells name? doesn't change for me for some reason...
I was thinking maybe to create an auto comment?
The thing is if i want to graph the data later on i will have to extract it further. Having it stored in a spare sheet is much easier.

So as mentioned above, if i have a macro that can date how much of the particular material has been withdrawn i would be able to sum then and have my monthly and yearly figures

Something like this maybe?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$A$2" Then

Dim CountSht As Worksheet
Set CountSht = Worksheets("Counters")
        
    'check if cell actually changed
    If CountSht.Range("E2") = Range("A2") Then
        Exit Sub
    Else
        CountSht.Range("E2") = Range("A2")
        CountSht.Range("F2") = Now()
    End If
    
    'count day events
    If CountSht.Range("C2") = CountSht.Range("B2") Then
        CountSht.Range("D2") = CountSht.Range("D2") + 1
    Else
        CountSht.Range("C2") = CountSht.Range("B2")
        CountSht.Range("D2") = 1
    End If
    
    'count week events
    If CountSht.Range("C3") = CountSht.Range("B3") Then
        CountSht.Range("D3") = CountSht.Range("D3") + 1
    Else
        CountSht.Range("C3") = CountSht.Range("B3")
        CountSht.Range("D3") = 1
    End If
    
    'count month events
    If CountSht.Range("C4") = CountSht.Range("B4") Then
        CountSht.Range("D4") = CountSht.Range("D4") + 1
    Else
        CountSht.Range("C4") = CountSht.Range("B4")
        CountSht.Range("D4") = 1
    End If
    
    'count year events
    If CountSht.Range("C5") = CountSht.Range("B5") Then
        CountSht.Range("D5") = CountSht.Range("D5") + 1
    Else
        CountSht.Range("C5") = CountSht.Range("B5")
        CountSht.Range("D5") = 1
    End If

End If

End Sub