time stamp

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

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.
 
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


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

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
Click to expand...

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
 
The mPrev is some code left over from a previous question that I forgot to
remove.

--
---
HTH

Bob

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



Rick Rothstein (MVP - VB) said:
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
Click to expand...

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
Click to expand...
 
Back
Top