time stamp

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 21 Jul 2007 16:49 GMT

I have done many time stamp formulas before, but am needing something
a little different....

In column J, I need a formula that would give me date stamp whenver
the adjacent cell in column I has the word "closed"

Or would this work better as a vb code for the sheet?

Thanx.

Reply to this Message

Bob Phillips - 21 Jul 2007 17:03 GMT

Certainly would

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "closed" Then
.Offset(0, 1).Value = Time
.NumberFormat = "hh:mm:ss"
End If
mPrev = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have done many time stamp formulas before, but am needing something
> a little different....
[quoted text clipped - 5 lines]
>
> Thanx.

Reply to this Message

Rick Rothstein (MVP - VB) - 21 Jul 2007 17:32 GMT

> Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "H1:H10" '<== change to suit
[quoted text clipped - 15 lines]
> Application.EnableEvents = True
> End Sub

A couple of thoughts.... One, you don't seem to be making use of the mPrev
variable anywhere except on the one line you assign something to it. Two,
maybe changing your If-Then statement to either this

If LCase$(.Value) = "closed" Then

or even this

If .Value Like "[Cc]losed" Then

would offer the user more flexibility.

Rick

Reply to this Message

Bob Phillips - 21 Jul 2007 17:46 GMT

The mPrev is some code left over from a previous question that I forgot to
remove.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Const WS_RANGE As String = "H1:H10" '<== change to suit
[quoted text clipped - 29 lines]
>
> Rick

Reply to this Message