CODEWITHSUNDEEP
vbaexcel
Ganda
Ganda

Reputation: 175

Excel Macro: Print Date when there is a change in data

I'm trying to have my macro print out the date when my data is updated in column H. Also I need it to print in the same row of the update as well, so assuming I update A2, I want the date to be printed in H2 etc.

I have this:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address <> Range("H" & Target.Row).Address Then        
        Range("H" & Target.Row).Value = Date
   End If
End Sub

For some reason, when I do end up changing something and hitting enter, I get a small delay, but the date never ends up printing in H. Not sure what I'm doing wrong here.

Upvotes: 0

Views: 902

Answers (2)

Tim Williams
Tim Williams

Reputation: 166156

Private Sub Worksheet_Change(ByVal Target As Range)
    Const DATE_COL As Long = 8
    Dim c As Range

    'don't trigger if user (eg) deletes a whole column!
    Debug.Print Target.Cells.Count
    If Target.Cells.Count > 1000 Then Exit Sub

    On Error GoTo haveError

    Application.EnableEvents = False
    For Each c In Target.Cells
        If c.Column <> DATE_COL Then
            c.EntireRow.Cells(DATE_COL).Value = Date
        End If
    Next c

haveError:
    Application.EnableEvents = True
End Sub

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You need:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> Range("H" & Target.Row).Address Then
        Application.EnableEvents = False
            Range("H" & Target.Row).Value = Date
        Application.EnableEvents = True
    End If
End Sub

Upvotes: 0

Related Questions