When specific value entered in one column, fill date column automatically

[ekm]

Hi all,

I need some help

When I enter the value "In Process" into column Q, I need column R to populate with the date. And similarly, when the value "Complete" is entered into column Q, I need column S to populate with the date.

I basically am trying to keep track of how long things in my list are marked 'in process'.

I found this for entering the date when ANY value is entered, but I am not familiar with VB enough to make it work how I want.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("Q2:Q1000")) Is Nothing Then

With Target(1, 2)

.Value = Date

.EntireColumn.AutoFit

End With

End If

End Sub

Any advice is greatly appreciated!
Emily

[mrice]

You were almost there.

Please Login or Register  to view this content.

[ekm]

Thank you so much -

This will work, but how I currently have the spreadsheet set up is for column Q to have data validation set with a drop down to select either 'In Process' or 'Complete'. I should have mentioned this earlier. I realize selecting the value does not put the date in, It seems like I have to type it into the cell?

This will work but I'm curious if it can work with the data validation on?

Also, I notice, if I drag values down (without hitting enter on the specific cell) the date doesn't get filled in.

Thank you again so much for your help. You have already save a ton of time.

[mrice]

Hmmmm...

It seems to work for me even with validation. Are you sure that the strings are exactly the same?

Please see attachment.

[ekm]

How strange - I repoened my file and it's working now. Thanks so much.

I'm out of luck when dragging down, right?

Also, if I add another case for if I change column Q back to blank, would something like this work?

Case Is = ""
Target.Offest(0, 1).Clear
Target.Offset(0, 2).Clear

(although obviously it doesn't because I wouldn't be asking if it did)

Thanks again : )

[mrice]

This should do it.

Please Login or Register  to view this content.

[ekm]

is that the same code as before?

[ekm]

Sorry - I see that it's different. But it doesn't seem to clear the dates if I remove the 'in process' or 'complete'

[ekm]

sorry - I think I'm a little delerious. What was the last code you sent supposed to do?

[mrice]

It was supposed to add the dates when you did a fill (i.e. the target range was greater than I cell)

The code below should do the deletes as well

Please Login or Register  to view this content.