I don't see why you would restrict the rows, see
http://www.mvps.org/dmcritchie/excel/event.htm#autodate
modified below to enter date into Column B when Column A
is changed. As setup will not enter a date if there is
already content in the cell in Column B.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Date
Target.offset(0, 1).numberformat = "yyyy-mm-dd"
End If
End Sub
It would actually be better to format the entire Column Bwith the date format you want rather than individually
formatting each cell in Column B -- won't matter that you
enter text into row 1 for a title.
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
"swatsp0p" wrote in message
...
Hi, RMCGAL:
I modified some code
(http://www.excelforum.com/showthread...day%27s+ date)
from Dave Peterson (thanks, Dave) to meet your needs.
This is worksheet event code, which means that it needs to be placed in
the appropriate worksheet code module, not a standard code module. To do
this, right-click on the sheet tab, select the View Code option from the
menu, and paste the code in.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a500")) Is Nothing Then
Exit Sub
End If
On Error GoTo errHandler:
Application.EnableEvents = False
With Target
If Not IsNumeric(.Value) Then
With .Offset(0, 1)
Value = Date
NumberFormat = "mm/dd/yyyy"
End With
End If
End With
errHandler:
Application.EnableEvents = True
End Sub
Note: this is set to cover the first 500 rows of your sheet. Enter a
text entry in any cell A1:A500, and TODAY'S date will automatically be
placed in the corresponding B cell. You can modify this to meet your
needs by editing the range in the code. Also, if you don't want column
AB, change that to desired column to 'read'. The Date Format can be
adjusted by changing the ".NumberFormat=" line to your desired format,
e.g.: "dddd, mmmm dd, yyyy" or "dd/mm/yy", etc.
Does this work for you?
Bruce
--
swatsp0p
------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=396578