CODEWITHSUNDEEP
excelvba
Lucy
Lucy

Reputation: 3

How to modify VBA that puts a timestamp in a cell based on a change made in another cell, to act on two columns?

I need this VBA:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Target, Range("M:M")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            cell.Offset(0, 1).Value = Now
            cell.Offset(0, 1).NumberFormat = "dd/mm/yyyy, hh:mm:ss"
        Next cell
    End If
    Application.EnableEvents = True
End Sub

To act on column M and produce a timestamp in column N and also act on column O and produce a timestamp in column P. I'm pretty new to VBA so would appreciate any help, Thanks!

Upvotes: 0

Views: 35

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

A single line of code change:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Target, Union(Range("M:M"), Range("O:O"))) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Target
            cell.Offset(0, 1).Value = Now
            cell.Offset(0, 1).NumberFormat = "dd/mm/yyyy, hh:mm:ss"
        Next cell
    End If
    Application.EnableEvents = True
End Sub

UPDATE:
But I would only loop over Intersect(), not over Target :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range, Sect As Range
    
    Set Sect = Intersect(Target, Union(Range("M:M"), Range("O:O")))
    
    If Not Sect Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Sect
            cell.Offset(0, 1).Value = Now
            cell.Offset(0, 1).NumberFormat = "dd/mm/yyyy, hh:mm:ss"
        Next cell
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions