Combining two similar VBA codes to run on same worksheet

[gearhead29]

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.

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

 

[My Aswer Is This]

This script will insert todays date into column "B" when a value is changed in column "A"
Not sure this is what you want. But it may help you.
Explain more if this is not what your attempting to do.

Modify column if needed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If Not Intersect(Target, Range("A3:A" & Lastrow)) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target.Offset(0, 1).Value = Date
End If
End Sub

 

[gearhead29]

Hi there to give you an example this is a tool to track workflow through our department. People dont always remember to update the date as they change things, I want a macro that when data is put into column a column b records the date of when it was added. It also has to deal with our update tracker, ie each time the project advances IE from design into production, a comment field is updated and a date added for that aswell but this is in column k and updates in column L. I have managed to get one of these to work but when i add the code again but change the columns in the copy it breaks the code.

 

[My Aswer Is This]

Gearhead.

The script I posted does the first part of what you wanted.

You did not even say if this part did what you want.

Did you even try using the script I posted?

 

[gearhead29]

Hi there sorry, I had tested it and it did provide exactly as required for the first part, I should have been clearer with my last post thank you for your help with getting part 1 working, I had already got it to do that with the code I had, but yours was much better executed. I am still unsure about how to achieve a code that can check to see if column a has changed and update column b with a date, and if column c has changed update column d.

Gears

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then Target.Offset(0, 1).Value = Date
If Not Intersect(Target, Range("C:C")) Is Nothing Then Target.Offset(0, 1).Value = Date
End Sub

 

[gearhead29]

That works brilliantly thanks so much

Gears

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

That works brilliantly thanks so much

Gears