gearhead29
New Member
- Joined
- Jul 1, 2016
- Messages
- 6
Hi guys fairly new to VBA coding, having only really started to need the functionality recently.
I have a worksheet with several columns where alternating columns are dates of when the previous was updated. I wanted a VBA to update the date when a value is changed starting fromt eh 3rd row down. I found one and altered it to my needs but cant get a second iteration of it to work on the second set of columns.
Second Column
Any help appreciated thanks
Gears
I have a worksheet with several columns where alternating columns are dates of when the previous was updated. I wanted a VBA to update the date when a value is changed starting fromt eh 3rd row down. I found one and altered it to my needs but cant get a second iteration of it to work on the second set of columns.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("K:K,X:X"), Range("2:" & Rows.Count)) Is Nothing Then
If Target.Count < Columns.Count Then
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
Dim r As Range
For Each r In Intersect(Target, Range("K:K,X:X"), Range("3:" & Rows.Count))
With r.Offset(0, 1)
.Value = Now 'use Now to retain the time as well as the date
.NumberFormat = "dd/mm/yyyy" 'change to what you prefer
End With
Next r
End If
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
Second Column
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("M:M,X:X"), Range("3:" & Rows.Count)) Is Nothing Then
If Target.Count < Columns.Count Then
On Error GoTo bm_Safe_Exit
Application.EnableEvents = False
Dim r As Range
For Each r In Intersect(Target, Range("M:M,X:X"), Range("3:" & Rows.Count))
With r.Offset(0, 1)
.Value = Now 'use Now to retain the time as well as the date
.NumberFormat = "dd/mm/yyyy" 'change to what you prefer
End With
Next r
End If
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
Any help appreciated thanks
Gears