Excel auto save macro

I am trying to create a macro that auto-saves my excel workbook whenever the value in a specific cell increases by 1.

I have the code for the auto-save whenever the cell changes which I will post below;

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
        ThisWorkbook.Save
    End If
End Sub

However, I can't seem to adapt the code for the macro to only execute when the value in the cell increases by one. I.e, cell value is 10, only save workbook when the cell value is 11 and so forth.

Thank you.

Answer

Try this code and revert back in case of any issues. Small assumption or constraint is that the sheet has been refreshed at least once from the database and the existing contents are not deleted thereafter, just refreshed again as and when needed. In case if you clear the data after save and before close and then open a blank sheet every time a different solution would be needed.

ALT + F11 --> VBA Editor --> ThisWorkbook --> General --> Declarations

Public Prev_Val As Variant

Workbook_Open() event

Private Sub Workbook_Open()
   ThisWorkbook.Prev_Val = Sheet1.Range("A2").Value
End Sub

Under Sheet1 or whatever is your Sheet Codename - Worksheet_Change(ByVal Target As Range) event. Replace Sheet1 by whatever is the Sheet Codename be it Sheet1 or Sheet2 or so.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
    If Sheet1.Range("A2").Value = ThisWorkbook.Prev_Val + 1 Then
        ThisWorkbook.Save
    End If
    ThisWorkbook.Prev_Val = Sheet1.Range("A2").Value
End If
End Sub

Now when you open the Workbook, the Public variable Prev_Val stores the value in A2 and uses it subsequently for comparison and keeps updating this value to whatever is the latest update in A2, till you sloe the Workbook.