Capture Dates

[cja65]

Hi,
I have a spreadsheet of projects that I am trying to record and save the dates each phase is completed. I want to use Private Sub Worksheet_Change(ByVal Target As Range) so it would update automatically but I can't figure out the code. The data is in a table with column S being the stage (1 through 8) and columns T through AA being the columns I want to store the dates each stage is completed. The table has projects added as needed so the code has to be able to self expand as the columns are added. If for example the stage of project 06 is changed from 2 to 3, then cell V7 will capture todays date - now(). If the same project stage is changed from stage 2 directly to stage 7 then the only cell that will capture the date is cell Z7, cells W7 to Y7 will remain empty. I appreciate any insight. Thanks.

 

[dreid1011]

The code won't put in the date. Looks like what I can find on line is that the worksheet CHANGE sub doesn't work on cells where there is a formula or a data validation. That being said, neither referring to column E or adding the =left(e4,1)+0 will do the trick. I am still working on it but am a novice to say the least. Thanks for all of your help BTW

You tried the updated code in post #8? I tested it on data validation in column E and it does in fact trigger the code when a value is selected from the drop down.

 

[Joe4]

How is column E being updated?
Is that also a formula? If so, what is that formula?
If column E is being manually updated (even if from a drop-down validation select box), the amended code dreid1011 should fire and work (as long as you also made the updates to the formula I mentioned).

 

[cja65]

Column E is a Data Validation pull down list

The current code is:

I added your code to make the cell a number =left(E4,1)+0 However the little green arrow still is in the corner.

My project managers update the table by changing the status via drop the drop down list in column E. I am trying to capture the date they change the status so I can track durations for different stages of projects..

 

[cja65]

Column E is a Data Validation pull down list
View attachment 116781

The current code is:
View attachment 116783
I added your code to make the cell a number =left(E4,1)+0 However the little green arrow still is in the corner.

My project managers update the table by changing the status via drop the drop down list in column E. I am trying to capture the date they change the status so I can track durations for different stages of projects..

with the code as is, i can't even hard type a number in column S and have the date be posted.

 

[dreid1011]

Sorry, try this update:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then
    Exit Sub
Else
    If Not Intersect(Target, Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)) Is Nothing And Left(Target,1)+0 > 0 And Left(Target,1)+0 < 9 Then
        Target.Offset(0, Left(Target.Value,1) + 14) = Date
    End If
End If
End Sub

 

[cja65]

Column E is a Data Validation pull down list
View attachment 116781

The current code is:
View attachment 116783
I added your code to make the cell a number =left(E4,1)+0 However the little green arrow still is in the corner.

My project managers update the table by changing the status via drop the drop down list in column E. I am trying to capture the date they change the status so I can track durations for different stages of projects..

given that column E is not just a number i.e. "1 - Prospect", with the Target >0 and Target <9 even return a true or false? This is why we have the left(E4,1)

I haven't written code since 1987 so sorry for the trouble.

 

[dreid1011]

given that column E is not just a number i.e. "1 - Prospect", with the Target >0 and Target <9 even return a true or false? This is why we have the left(E4,1)

I haven't written code since 1987 so sorry for the trouble.

Sorry, see my edits to code in post #15. I was being dumb and not taking into account column E contained more than the number.

 

[cja65]

Sorry, try this update:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then
    Exit Sub
Else
    If Not Intersect(Target, Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)) Is Nothing And Left(Target,1)+0 > 0 And Left(Target,1)+0 < 9 Then
        Target.Offset(0, Left(Target.Value,1) + 14) = Date
    End If
End If
End Sub

It still won't change the value.

 

[cja65]

It still won't change the value.
View attachment 116784

ha, ya I just changed mine to this and it worked as well. I will add the +0.

Thanks!!

 

[dreid1011]

It still won't change the value.
View attachment 116784

Did you copy the code before I made the most recent update? It should work now if you copy the code in post #15 again.

Edit: Looks like you got it.