date and time stamp on command

[J.W. Aldridge]

Hi.

I have the formula in which if I update a cell in a row, column A will
time stamp the change.

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A" & Target.Row) = Now()
End Sub


However, I would like to have a time stamp in column A, whenever the
same row (column B) is updated with a "Y". I am keeping a daily log and
want to be able to control the date and time stamped by placing a Y in
column B. I want to know what time a Y was placed on the row.

In short terms, Y = date/time stamp, no Y, no date/time stamp.

Example below:

A B C
11/16/2006 10:59 Y Sheila
poked me in the eye.

I poked her back!
11/16/2006 11:00 Y She poked
me again!


Any suggestions?

 

[Ardus Petus]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rChanged As Range
Dim rCell As Range
Set rChanged = Intersect(Target, Columns("B"))
If Not rChanged Is Nothing Then
For Each rCell In rChanged.Cells
If LCase(rCell.Value) = "y" Then
Cells(rCell.Row, "A") = Now()
End If
Next rCell
End If
End Sub


Cheers,

 

[J.W. Aldridge]

When I update a row, all the times and dates change.
Any way to keep the previous ones (date & times) from changing?

 

[Ardus Petus]

My code updates only the rows with "Y" or "y" in column B.

It does not change other rows

 

[J.W. Aldridge]

My code updates only the rows with "Y" or "y" in column B.

It does not change other rows

Right. And I do not want the other rows to change either however...

Currently,
If row 1 has a time stamp of 11/16/06 2:32 PM when I put the "y" in
column B.

It changes to the current time 11/16/06 2:33 PM when I update row 2
column B with the "y".

I need this to be like a timed diary of each separate event with a "Y"
Any way to record the first time a y is placed in (column B) row and
have that time not to change if I should add a Y when I go to the next
row?

thanx

 

[Ardus Petus]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rChanged As Range
Dim rCell As Range
Set rChanged = Intersect(Target, Columns("B"))
If Not rChanged Is Nothing Then
For Each rCell In rChanged.Cells
If LCase(rCell.Value) = "y" Then
With Cells(rCell.Row, "A")
If IsEmpty(.Value) Then .Value = Now()
End With
End If
Next rCell
End If
End Sub

HTH,

 

[J.W. Aldridge]

.......worked like a charm!

THANX!