A change in one cell generates (todays) date in another

[ihatetheredskins]

We have a spreadsheet where one cell (A) has a drop down list of choices.
Cells B C D E correspond to the list. So the list is b,c,d,e and the next
columns are b,c,d,e headers. If the cell A changes from one choice to
another can that date the change occured be inputed into the corresponding
cell?

Thanks for all the help you guys give.

 

[Bernie Deitrick]

Well, being a Redskins fan, I'm not sure if I should help you or not.....



You need to use a worksheet change event to do that: for example, for a
change made to
any cell in column A, the date when the entry is made or changed is stored
in column B
using this code:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("A:A"))
Cells(myCell.Row, 2).Value = Now
Cells(myCell.Row, 2).NumberFormat = "mm/dd/yy hh:mm:ss"
Next myCell
Application.EnableEvents = True
End Sub


Copy this code, right-click on the worksheet tab, select "View Code" and
paste the code in the window that appears.


HTH,
Bernie
MS Excel MVP

 

[ihatetheredskins]

I am summoning a lot of strength to respond to a redskins fan....

Thanks for the quick response below. I added the code. The change takes
place in column Q and the date appears in column B. How do I tell the code
that if say the drop down equals "Invoice phase" that it adds the date to
column V? Or if the drop down choice is "Approval phase" it puts the date in
column T?

The code is great, I guess I am not aware of out to manipulate if further.

 

[Bernie Deitrick]

I don't really follow football anymore - more of a soccer guy these days
;-)

Anyway, try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("Q:Q")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("Q:Q"))
If myCell.Value = "Invoice Phase" Then
Cells(myCell.Row, 22).Value = Now
Cells(myCell.Row, 22).NumberFormat = "mm/dd/yy hh:mm:ss"
End If
If myCell.Value = "Approval Phase" Then
Cells(myCell.Row, 20).Value = Now
Cells(myCell.Row, 20).NumberFormat = "mm/dd/yy hh:mm:ss"
End If
Next myCell
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP

 

[ihatetheredskins]

Well I am glad you watch real "football" now.

Thank you so much for the help. I did as you said and add some more lines
and it works perfectly. This will be a big help for us. Thanks again, I am
very grateful.

 

[Bernie Deitrick]

Thanks again, I am
very grateful.

You're quite welcome. Thanks for letting me know that you succeeded...

Bernie