Both the code works and will do what I need,
Thanks a lot to all of you for the help. This group is great.

Best,
AM

"Rick Rothstein (MVP - VB)" wrote:

If I understand your question correctly, I think this VBA code will work for
you...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
With Me.Cells(Rows.Count, 1).End(xlUp)
If .Value < Date Then .Offset(1, 0).Value = Date
End With
End If
End Sub

It will place today's date in the next blank row of column A whenever a
change is made (anywhere) in column B provided the last filled row in column
A does not contain today's date already (this prevents multiple entries of
today's date for multiple changes in column B within the same day).

Rick


"AM" wrote in message
...
I want to do it when any value is entered in col B

Thanks

"Zone" wrote:

You could do this with a macro, but how you implement it would depend on
when you want to put the date in
- Every time the file is opened?
- Each time the sheet is displayed?
- When the sheet calculates?
- When a button is pushed?
James

"AM" wrote in message
...
Some one tried to help me with this, but did not worked, so sorry for
reposting

My Original Message
I would like to see today's date in
column A everyday in new cell, maintaining previous cell with
yesteray's
date
and previous before previous cell with day before yesterday's date and
so
on.
So every weekday you see today's date in the cell.
e.g

08/20/2007
08/21/2007
08/22/2007 Today
Will show up tomorrow as 8/23/2007
Will show up Friday as 8/24/2007

Will be one blank row for saturday & Sunday
Will show up Monday as 8/27/2007

Thanks
AM


The answer was

Dim mLastRow As Integer

Dim mdate As Date
mdate = Date
If Weekday(mdate) 1 Then
mLastRow = Range("A65000").End(xlUp).Row + 2
Else
mLastRow = Range("A65000").End(xlUp).Row + 1
Range("A" & mLastRow).Value = mdate
End If

This will automatically check for saturday and sunday and will skip a
row
while inseting new date. I have tested this.
Hope this helps!!
But for some reason ot does not work for me, Any further help would be
greatly appriciated.

Thanks
AM