Record Date and Time when cell is changed, but remove record when cell is empty

[normajean]

Hi all,

I'm completely new to VBA but I think I may need to use it. I'm entering data into column "P". I need the corresponding cells in column "I" to display the date and time that data is entered into "P". I found this code that has worked well:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 Then
Application.EnableEvents = False
Cells(Target.Row, 9).Value = Date + Time
Application.EnableEvents = True


End If

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub


The only problem with this is that when I remove the data from column "P", the date and time is still there. I want a modified version of this VBA (or a totally different one) that says something like if "P" is empty, then "I" will be empty.

Any help would be really appreciated!

 

[VBA Geek]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub
    If Target.Value = vbNullString Then
        Target.Offset(, -7).Value = vbNullString
    Else
        Target.Offset(, -7).Value = Now
    End If
End Sub

 

[normajean]

Hey, thanks for the response. I tried this but it highlighted the word "Intersect" in the third line and said "Compile Error: Argument not optional".

 

[normajean]

Hey it's working!!! I just typed it in wrong.. Thanks so much!!