Auto add date in adjacent cell when data inputted

Gavin Harrison

New Member
Joined
May 2, 2017
Messages
26
Hi All.

I have the VBA code below whereby when data is entered into a cell in column C for example, the date that the info is entered is automatically entered into the cell in column B.

I would like to replicate this for columns C:V. I have changed the code to C:V instead of C:C but it enters the date in every cell on the row before that column. For example if I enter data into N7, it is adding the date into M7, L7, K7, J7, I7 and so on rather than just M7.

Any advise would be appreciated.

Thanks
Gavin

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim xRg As Range, xCell As Range
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("C:C")) Is Nothing) Then _
Target.Offset(0, -1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("C:C"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, -1) = Date
Next
End If
Application.EnableEvents = True
End If


End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,729
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, [C:V]) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(0, -1) = Date
    Application.EnableEvents = True
End If
End Sub
 

Forum statistics

Threads
1,084,776
Messages
5,379,812
Members
401,629
Latest member
LEMANOIS

Some videos you may like

This Week's Hot Topics

Top