Automatic date in a cell, when the corresponding row is updated or changed

[Sarahd1]

Hi everyone. I have been trying to figure out a VBA code that will automatically input the date which the corresponding row is updated. I need the code in column P, starting in row 4, and I already have code in the document for some other columns. The goal here is to be able to see the last date that each row was updated.
Can anyone please advise a code that will do this? The rows are a mixture of free text and some dependent drop down lists. I will post the code and a screenshot of the code, that I already have below, can you please advise where to put the additional code for the dates? I have never used two different codes in one document before. Thank you!!

Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 2018/06/04

    Application.EnableEvents = False

    If Target.Column = 2 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 4 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 5 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    If Target.Column = 6 And Target.Validation.Type = 3 Then
        Target.Offset(0, 1).Value = ""
    End If

    Application.EnableEvents = True
    
End Sub

 

[Sarahd1]

And do you want it to work only if the cell changed has a Data Validation applicable to it, or any cell in the range A:O?

Any cell in the range A:O. They are a mix of data validation and free text

 

[Sarahd1]

Any cell in the range A:O. They are a mix of data validation and free text

Just to confirm - when I say for any cell, I am only referring to the portion of code for the dates.
I need the date to update if any cell in the range is changed or a new row of data is added.
But the previous code for the data validation drop downs (columns, 2, 4, 5, 6), I need that to remain as it was before. Not for all cells in the range A:O.

 

[kevin9999]

OK, give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("A:O"), Target) Is Nothing Then
    Application.EnableEvents = False
        If Target.Validation.Type = 3 Then
            Target.Offset(, 1) = ""
            Range("P" & Target.Row).Value = Date
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[Sarahd1]

OK, give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("A:O"), Target) Is Nothing Then
    Application.EnableEvents = False
        If Target.Validation.Type = 3 Then
            Target.Offset(, 1) = ""
            Range("P" & Target.Row).Value = Date
        End If
        Application.EnableEvents = True
    End If
End Sub

Thanks Kevin
The code before this one was more close to what I needed, as it affected the correct drop downs. The only change I needed was that if any cell in the row was updated or changed (starting in row 4), the date would update in column P.

I had two issues with this code -
Firstly, this is now affecting the drop downs between C and D. C and D are not dependent lists, and now if something is already selected in column D, and column C is changed, then D blanks. These columns aren't connected and I don't need changes in column C to affect column D.

Secondly, After I used the code, I typed in one of the free text cells to test whether the date would work, and I was given error code 1004. I clicked debug and it highlighted the error row as (bold below). I deleted the code and replaced it with the previous code, but now that won't work either

OK, give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("A:O"), Target) Is Nothing Then
    Application.EnableEvents = False
[B]        If Target.Validation.Type = 3 Then[/B]
            Target.Offset(, 1) = ""
            Range("P" & Target.Row).Value = Date
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[kevin9999]

Thanks Kevin
The code before this one was more close to what I needed, as it affected the correct drop downs. The only change I needed was that if any cell in the row was updated or changed (starting in row 4), the date would update in column P.

I had two issues with this code -
Firstly, this is now affecting the drop downs between C and D. C and D are not dependent lists, and now if something is already selected in column D, and column C is changed, then D blanks. These columns aren't connected and I don't need changes in column C to affect column D.

Secondly, After I used the code, I typed in one of the free text cells to test whether the date would work, and I was given error code 1004. I clicked debug and it highlighted the error row as (bold below). I deleted the code and replaced it with the previous code, but now that won't work either

OK, try the following. Replace all existing code with this code, save the file then test.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("A:O"), Target) Is Nothing Then
    Application.EnableEvents = False
        Range("P" & Target.Row).Value = Date
        Select Case Target.Column
            Case Is = 2, 4, 5, 6
                If Target.Validation.Type = 3 Then
                    Target.Offset(, 1) = ""
                End If
        End Select
    End If
    Application.EnableEvents = True
End Sub

 

[Sarahd1]

OK, try the following. Replace all existing code with this code, save the file then test.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 And Not Intersect(Range("A:O"), Target) Is Nothing Then
    Application.EnableEvents = False
        Range("P" & Target.Row).Value = Date
        Select Case Target.Column
            Case Is = 2, 4, 5, 6
                If Target.Validation.Type = 3 Then
                    Target.Offset(, 1) = ""
                End If
        End Select
    End If
    Application.EnableEvents = True
End Sub

Thanks Kevin! that one seemed to do the trick
However, I am now getting an error again, and I don't know why. I will attach screenshots. Note, I amended the code to remove column 4 as I made

some changed to the document and no longer needed that column. You can see in the sceenshots the error I am getting, and where the error is showing up. I clicked on 'debug' from the error window, and that row is highlighted. Any thoughts? Is this a result of the code or something else I have done?

 

[kevin9999]

What did you do exactly to get the above error? Did you make a change in a column, if so which one, and what change did you make?

 

[Sarahd1]

What did you do exactly to get the above error? Did you make a change in a column, if so which one, and what change did you make?

I can't remember exactly, but I think it was when I was typing free text in one of the columns or selecting the drop downs. it has happened with previous codes, definitely when I entered free text in a cell or used the drop downs. I was having this issue previously with previous codes as well.
I didn't change any of the formatting of the columns (i.e change where drop downs appeared or what cells were free text. It was simply using the document as it will be used (it is a template so data will be entered to the columns).

 

[kevin9999]

OK. Unfortunately, I can't replicate the problem you're having on the sheet I created to test the code - but then again, I'm only guessing the actual layout etc. Could you please provide a copy of the actual sheet using the XL2BB Tool, or alternatively, share your file via a file-sharing website such as Dropbox or something similar?