NGoulette
01-09-2018, 01:44 PM
Hello.
I've looked all over this site as well as others for a solution and unable to locate a solution. My VBA skills are sorely lacking so I'm unable to figure out how to make this work from what others have used.
So this code works fine as is but it doesn't do exactly what I want. It originally was updating cells in the same column below the watched cell based on the offset specified. I managed to alter it to update the cell based on the offset in the same row. The result here is that updates for column F put a time stamp in column O, which is EXACTLY what I want. However, due to the fact that it's taking the offset for each watched cell, the update made to column G put a time stamp in column P, H into Q and so on.
What I would like is that any updates made to F, G, H, I, J, M & N result in a time stamp being placed in O on the same row.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngStatus As Range
Dim c As Range
Dim rngChange As Range
'Which row has the status cells we want to monitor?
Set rngStatus = Range("F:J,M:N")
Set rngChange = Intersect(rngStatus, Target)
'Did user change a cell we care about?
If rngChange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
'Put timestamp in column O if column F is changed
rngChange.Offset(columnOffSet:=9).Value = Now
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Any assistance would be greatly appreciated.
Please let me know if any further clarification is needed.
EXCEL 2016
OS: Windows 7 Ent
Regards,
Nathan
I've looked all over this site as well as others for a solution and unable to locate a solution. My VBA skills are sorely lacking so I'm unable to figure out how to make this work from what others have used.
So this code works fine as is but it doesn't do exactly what I want. It originally was updating cells in the same column below the watched cell based on the offset specified. I managed to alter it to update the cell based on the offset in the same row. The result here is that updates for column F put a time stamp in column O, which is EXACTLY what I want. However, due to the fact that it's taking the offset for each watched cell, the update made to column G put a time stamp in column P, H into Q and so on.
What I would like is that any updates made to F, G, H, I, J, M & N result in a time stamp being placed in O on the same row.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rngStatus As Range
Dim c As Range
Dim rngChange As Range
'Which row has the status cells we want to monitor?
Set rngStatus = Range("F:J,M:N")
Set rngChange = Intersect(rngStatus, Target)
'Did user change a cell we care about?
If rngChange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
'Put timestamp in column O if column F is changed
rngChange.Offset(columnOffSet:=9).Value = Now
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Any assistance would be greatly appreciated.
Please let me know if any further clarification is needed.
EXCEL 2016
OS: Windows 7 Ent
Regards,
Nathan