Data Validation Drop Down List

[wthornton59]

Hi,
I have a column on a Worksheet I'm developing that has a column where the person assigned the task's name is selected from a drop down list created from a named region using Data Validation. That part works perfectly. What I need is when someone is selected in that column, for the next column to be populated with the date. I can't figure out how to catch the selection to trigger the date being put in. I have been trying to do this using the Worksheet_Change sub routine. Any ideas would be great.

 

[hiker95]

wthornton59,

What column letter are the cells with the data validation?

What is the first, and last row, of the cells with the data validation?

What is the date format for the cell to the right?

 

[wthornton59]

hiker95,
Column with Data Validation is D

Rows are 5 to EOF

Date format is mm/dd/yyyy

Thanks,

 

[hiker95]

wthornton59,


Sample data before making a selection from your validation list beginning in cell D5:

Excel Workbook
	D	E
5
6
7
8
9
10
11
12
13
14
Sheet1

Then, after making some selections from the validation list:

Excel Workbook
	D	E
5	C	02/28/2011
6
7
8	E	02/28/2011
9
10
11
12
13	G	02/28/2011
14
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 02/28/2011
' http://www.mrexcel.com/forum/showthread.php?t=532025
If Intersect(Target, Range("D5:D" & Cells(Rows.Count, 4).End(xlUp).Row)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
With Target.Offset(, 1)
  .Formula = "=Today()"
  .NumberFormat = "mm/dd/yyyy"
  .Value = .Value
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Then make selections beginning in cell D5 down.