annoying updating time!

[AJSullivan]

I am relativley new to Excel forumlas so im hoping this is a simple
problem

I was a simple spreadsheet that will enter the current time into a cell
when a value is put in.

the formula I have is:

=IF(B3>1,NOW(),"")

this gives me the current time if there is a value greater than one in
there - PERFECT...except...

it updates everytime i enter something. I need the date to stay where
it is if i make changes to other cells.

I looked through the web and am having trouble


thanks in advance for the help

Alex

 

[Jason Morin]

You need a worksheet_change event macro that will stamp
the time into the cell. It'll trigger every time B3 is
worked with, and update the time stamp in C3 if B3 is
greater than 1.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
With Target
If .Value > 1 Then
Application.ScreenUpdating = False
.Offset(0, 1).Value = Now
.Offset(0, 1).NumberFormat = "h:mm AM/PM"
Application.ScreenUpdating = True
End If
End With
End Sub

Right-click on the worksheet tab, View Code, and paste in
the code above.

HTH
Jason
Atlanta, GA

 

[Don Guillett]

right click sheet tab>view code>insert this.chg b4 to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then Range("b4") = Time
End Sub

 

[Dave Peterson]

Muscle memory can be dangerous!

I bet you meant:

application.enableevents = false
'''''
application.enableevents = true

(Or autocompletion can be dangerous!)