date and time stamp on command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 16 Nov 2006 17:31 GMT

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?

Reply to this Message

Ardus Petus - 16 Nov 2006 17:48 GMT

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,
--
AP

> Hi.
>
[quoted text clipped - 23 lines]
>
> Any suggestions?

Reply to this Message

J.W. Aldridge - 16 Nov 2006 19:26 GMT

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

Reply to this Message

Ardus Petus - 16 Nov 2006 19:41 GMT

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

It does not change other rows

--
AP

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

Reply to this Message

J.W. Aldridge - 16 Nov 2006 20:36 GMT

> 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

Reply to this Message

Ardus Petus - 16 Nov 2006 20:44 GMT

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,
--
AP

>> My code updates only the rows with "Y" or "y" in column B.
>>
[quoted text clipped - 15 lines]
>
> thanx

Reply to this Message

J.W. Aldridge - 16 Nov 2006 21:36 GMT

......worked like a charm!

THANX!

Reply to this Message

Rate this thread: