Search code examples
excelvba

Delete Worksheet_Change cell data if cell data in another cell is deleted

I needed to create a timestamp to track when information is entered in a cell.

I found the following code.

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("K4:K148")) Is Nothing Then
        Target.Offset(0, 1) = Now
    End If
End Sub

When I delete the data in column K to start a fresh file for the next day, the timestamp in column L updates.

How can I delete the timestamp in column L if the corresponding cell in column K is deleted?

Example, I enter data in to K4, I get a timestamp in L4.
If I delete the data in K4, I want L4 to be deleted.

Picture showing data entered into column K (1, 2 and 3) and column L showing the timestamps.
enter image description here

Picture showing 3 being deleted with the timestamp updating.
enter image description here

Solution

  • Using IsEmpty. Len(cell.Value)=0 as mentioned in comments would also work.

    Sub Worksheet_Change(ByVal Target As Range)
   Dim rng As Range
   Set rng = Intersect(Target, Me.Range("K4:K148"))

   If Not rng Is Nothing Then
       On Error GoTo SafeExit
       Application.EnableEvents = False
    
       Dim cell As Range
       For Each cell in rng
           cell.Offset(,1).Value = IIf(IsEmpty(cell.Value), "", Now)
       Next
   End If

SafeExit:
   Application.EnableEvents = True
End Sub