Time stamp in a fixed cell when content of specific cells in row are changed

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.

Code:

---

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

And by all means, if the above method isn't the most efficient way to achieve my goal, I am totally open to something else entirely. This is the closest I could get to what I wanted but I realize it may not be ideal.:)

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range) 
    Application.EnableEvents = False
   
    If Intersect((Range("F:J,M:N"), Target) is nothing then Exit Sub
    Cells(Target.Row, "O") = Now
   
    Application.EnableEvents = True
End Sub

---

Thank you SamT for the reply.

When I update the code with what you've posted, the IF line is red and I get a compile error stating that a close parenthesis was expected where the comma is in that line.

Attachment 21325

Any thoughts?

It seems there are 3 open and only 2 close parentheses and I've played around with adding a 3rd but I am not getting it.

Sweet, I figured it out! Seems the TARGET and RANGE were reversed.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)    Application.EnableEvents = False
   
    If Intersect(Target, Range("F:J,M:N")) Is Nothing Then Exit Sub
    Cells(Target.Row, "O") = Now
   
    Application.EnableEvents = True
End Sub

---

Thanks again SamT for getting me going in the right direction!
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Intersect(Target, Range("F:J,M:N")) Is Nothing Then Exit Sub
Cells(Target.Row, "O") = Now

Application.EnableEvents = True
End Sub

Quote:

---

the IF line is red and I get a compile error stating that a close parenthesis was expected where the comma is in that line.

---

Oops! Too many Open-Parens

Code:

---

If Intersect(Range("F:J,M:N"), Target)

---

VBA's Error statements are not very precise. All that message means is that there is an error involving Parens somewhere on that line.

Quote:

---

Seems the TARGET and RANGE were reversed.

---

Actually, no. Their order is not relevant. You do have the correct use of Parens. On set around the Intersect parameters, and one set around the Range Parameters.

Noticably, extraneous sets of Parens won't break the code, as long as the total syntax is good
If Intersect((Range("F:J,M:N")), (Target)) 'Good syntax

Ah, very good SamT, thank you for the clarification. As I said, my skills are lacking in this area. :)

I had this working but haven't used it for a long while, now the field is not actually updating when changes are made to the designated columns.
Is there something in Excel itself that could be stopping the modified date from populating correctly?