Removing timestamp if data is deleted from the range-CodePudding

I have this code that I am using to get a timestamp on sheet2 every time I enter data in a range on sheet1.

This is working great, however, I want it to remove the time stamp if the data is deleted. I also want it to go back to the previous timestamp if there was existing data prior. Please see below the two scenarios that describe it a bit better.

e.g. 1 I enter quantities on sheet1. I7: 1 This prompts the code to put a timestamp in E7 on sheet2. If I delete this value in I7 on sheet1, the timestamp stays there in E7 on sheet2. I want it to delete it if there is no value in Row 7 on Sheet1.

e.g. 2 There is an existing value in I7 from 26.04.22 and I then enter another value in J7 on 27.04.22 and it updates the timestamp in E7 on sheet2 to 27.04.22 as it should. However, I realised I have made a mistake and this is not the correct cell I wanted to put this data into. So I delete the value in J7. Once I do this, I want the Timestamp in E7 to go back to 26.04.22 showing me when the value in I7 was entered.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim srg As Range: Set srg = Range("I7:NF1000")
Dim irg As Range: Set irg = Intersect(srg, Target)
If irg Is Nothing Then Exit Sub

Set irg = Intersect(irg.EntireRow, Columns("E"))

Dim TimeStamp As Date: TimeStamp = Now

Dim arg As Range
For Each arg In irg.Areas
    Sheet2.Range(arg.Address).Value = TimeStamp
Next arg

End Sub

CodePudding user response：

The following code will add/remove the time stamp on Sheet 2 in a less convoluted way than your original code.

Your scenario 2 is not doable, since the time stamp is overwritten and cannot be retrieved, unless you want to build a database of historic values, which would require a lot more code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
    If Not Intersect(Target, Range("I7:NF1000")) Is Nothing Then
        For Each rng In Target
            If rng = "" Then
                ws.Range("E" & rng.Row).ClearContents
            Else
                ws.Range("E" & rng.Row) = Now
            End If
        Next rng
    End If
End Sub