VBA to delete cell data if cell data in another cell is deleted

June 15, 2023

I needed to create a timestamp if data was entered into a cell to track when information is being added.

I found the following code that works perfectly, mostly.

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

My problem now is that when I delete the data in column K to start a fresh file for the next day, the timestamp in column L updates even when I delete the data. Is there an update to the code that can 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 of when K4 was entered. If I delete the data in K4, I want L4 to delete.

Pictures here showing data entered into column K (1,2,3) and column L showing the timestamps. Second picture shows 3 being deleted with the timestamp updating.

>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