I want to automatically enter a static time in a cell when a value is entered
into another adjacent cell. The Now() function is dynamic and will change if
another field in the sheet is updated.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "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
.Offset(0, 1).Value = Time
.Offset(0, 1).NumberFormat = "hh:mm:ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kefee85" wrote in message
...
I want to automatically enter a static time in a cell when a value is
entered
into another adjacent cell. The Now() function is dynamic and will change
if
another field in the sheet is updated.

The following Event macro will fire whenever you change a cell in the
sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.cells(1,1).column = 14 and target.cells.count = 1 Then
Target.Offset(0, 1).Value = Now
endif
End Sub

Offset(0,1) refers to one cell to the right of the cell changed. The
check for
Target.cells(1,1).column = 14
for the moment will do so if the column in question is the 14th one
(N:N). Change the number to whatever column you want.

To install:
Right click the sheet tab. Select View Code... Paste the above code.

HTH
Kostis Vezerides


kefee85 wrote:
I want to automatically enter a static time in a cell when a value is entered
into another adjacent cell. The Now() function is dynamic and will change if
another field in the sheet is updated.