Automatically update the date in Excel cell on worksheet updation

example for Automatically updating the date in Excel cell on worksheet updation

This macro will got called when worksheet chabged and it will update the corresponding fields of row automactically with updated date.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Row As Range
   Dim FocusRange As Range
   
   Application.EnableEvents = False
   Set FocusRange = Intersect(Target, [C:C])
   If Not FocusRange Is Nothing Then
      For Each Row In Target.EntireRow.Rows
         If Len(Row.Cells(1, 1)) = 0 Then Row.Cells(1, 1) = Int(Now)
      Next Row
   End If
   Set FocusRange = Intersect(Target, [C:L])
   If Not FocusRange Is Nothing Then
      For Each Row In Target.EntireRow.Rows
         Row.Cells(1, 2) = Int(Now)
      Next Row
   End If
   Application.EnableEvents = True

End Sub

For multiple rows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = 0
    If Not Intersect(Target, Range("C:L")) Is Nothing Then
        If Target.Rows.Count > 1 Then
            If Target.Column = 3 Then Cells(Target.Row, 2).Resize(UBound(Target.Value)).Value = Date
            Cells(Target.Row, 1).Resize(UBound(Target.Value)).Value = Date
        Else
         If Target.Column = 3 Then Cells(Target.Row, 1).Value = Date
        Cells(Target.Row, 2).Value = Date
        End If
    End If
    Application.EnableEvents = 1
End Sub

By Perry    Popularity  (3654 Views)