Automatic add Time and Date

pure vito

New Member
Joined
Oct 7, 2021
Messages
42
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,596
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
42
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
56,596
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Forum statistics

Threads
1,176,504
Messages
5,903,431
Members
435,029
Latest member
excelvbanoob420

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