Formula or VBA to stop the NOW function

[Sparky]

I have the NOW function in cell A1
In cell A2 the time is entered manually
In cell A3 I have A1-A2 to display the amount of time elapsed
In cell A4 I would like to be able to enter the letter C once a task has been completed in my work place and once the letter C has been entered the time elapsed would then remain static.

Thanks in advance

 

[VoG]

Try this. Right click the sheet tab and select View Code then paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And UCase(Target.Value) = "C" Then
    Application.EnableEvents = False
    Target.Offset(0, -3).Value = Now
    Application.EnableEvents = True
End If
End Sub

then close the code window using the X.

 

[Sparky]

Thanks for your quick reply.

I have pasted your code as you suggested but the time elapsed in A3 continues to increase as the minutes go by every time the sheet is recalculated.

 

[VoG]

Sorry, I had confused rows with columns

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 4 And UCase(Target.Value) = "C" Then
    Application.EnableEvents = False
    Target.Offset(-3, 0).Value = Now
    Application.EnableEvents = True
End If
End Sub

When you enter C (or c) in row 4 the NOW() formula in row 1 will be replaced by a static Now.

 

[Sparky]

It worked OK the first time and the NOW function remained static. But somehow it no longer works.

 

[VoG]

Press ALT+F11 to open the Visual Basic Editor. Press CTRL + G to open the Immediate Window. Type in

? application.enableevents

and press Enter. Does that return False? If so type

Application.EnableEvents = True

and press Enter. Then close the code window and test whether the event code is now working.

 

[Sparky]

VoG II

Thanks for the reply. Works fine.