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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ihatetheredskins - 12 Jun 2007 20:45 GMT

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.

Reply to this Message

Bernie Deitrick - 12 Jun 2007 21:15 GMT

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

> 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
[quoted text clipped - 3 lines]
>
> Thanks for all the help you guys give.

Reply to this Message

ihatetheredskins - 12 Jun 2007 21:58 GMT

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.

> Well, being a Redskins fan, I'm not sure if I should help you or not.....
>
[quoted text clipped - 29 lines]
> >
> > Thanks for all the help you guys give.

Reply to this Message

Bernie Deitrick - 13 Jun 2007 01:28 GMT

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

>I am summoning a lot of strength to respond to a redskins fan....
>
[quoted text clipped - 45 lines]
>> >
>> > Thanks for all the help you guys give.

Reply to this Message

ihatetheredskins - 13 Jun 2007 15:39 GMT

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.

> I don't really follow football anymore - more of a soccer guy these days
> ;-)
[quoted text clipped - 71 lines]
> >> >
> >> > Thanks for all the help you guys give.

Reply to this Message

Bernie Deitrick - 13 Jun 2007 15:56 GMT

> Thanks again, I am
> very grateful.

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

Bernie

Reply to this Message