Automatic add Time and Date

pure vito

New Member
Joined
Oct 7, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Morning/Afternoon all,

I have this code that when column B is populated column A will be populated with the time and date,

it works great but only on one entry if i try to paste to multiple cells in column B it does not add the time and date to each adjacent cell in column A,

Might any of the professionals amongst you have an answer to this issue thanks in advance and i am very new to VBA i appreciate the help.



VBA Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Extendoffice 2017/10/12
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    If (Target.Count = 1) Then
        If (Not Application.Intersect(Target, Me.Range("B:B")) Is Nothing) Then _
            Target.Offset(0, -1) = Format(Now(), "yyyy-MM-dd hh:mm:ss")
        Application.EnableEvents = False
        Set xRg = Application.Intersect(Target.Dependents, Me.Range("B:B"))
        If (Not xRg Is Nothing) Then
            For Each xCell In xRg
                xCell.Offset(0, -1) = Format(Now(), "yyyy-MM-dd hh:mm:ss")
            Next
        End If
        Application.EnableEvents = True
    End If
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,261
Office Version
  1. 365
Platform
  1. Windows
Do you really need that 'Dependents' section or would this do?

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range
  
  Set Changed = Intersect(Target, Columns("B"))
  If Not Changed Is Nothing Then Changed.Offset(0, -1) = Format(Now(), "yyyy-MM-dd hh:mm:ss")
End Sub
 

pure vito

New Member
Joined
Oct 7, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
That's Perfect thank you so much taking the time really appreciate it,
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,261
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,781
Messages
5,766,430
Members
425,353
Latest member
ExcelRalph

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top