Automatically adding the date/time to a cell when another cell is updated AND clearing it when this-CodePudding

I'm creating excel file for company reports. I've borrowed script from this MS Support thread - LINK
It looks like that:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1")) Is Nothing Or _
    Not Intersect(Target, Range("B1")) Is Nothing Or _
    Not Intersect(Target, Range("C1")) Is Nothing Or _
    Not Intersect(Target, Range("D1")) Is Nothing Or _
    Not Intersect(Target, Range("E1")) Is Nothing Then

    Target.Offset(1, 0) = Now
 End If

End Sub

It works fine but now i want target cells to be cleared after cleaing main cells.
Now when for example i erase A1, the time in A2 stays there.
I'm quite new in Excel and hope for some help :)
Have a good day!

...............................

CodePudding user response：

You are looking for an IsEmpty check in your code:

If IsEmpty(Target.Value) Then Target.Offset(1, 0).ClearContents Else Target.Offset(1, 0) = Now

As a whole:

Sub Worksheet_Change(ByVal Target As Range)

 If Not Intersect(Target, Range("A1:E1")) Is Nothing Then

    If IsEmpty(Target.Value) Then Target.Offset(1, 0).ClearContents Else Target.Offset(1, 0) = Now

 End If

End Sub