That link has the right formula but now I am getting a circular
reference error message because it only works in the adjacent cell.
how can I get it to work in the cell that I enter the time in? I need
the the cell to change after I enter the time. If I need an event
triggered macro then what exactly is that?
To enter this code, right click on the sheet tab and select View Code. Paste
the code below into the window that opens.
Change AOI to reflect the range in which you may be entering time.
==============================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range
Set AOI = [A:A] 'times will be rounded if they are entered in column A
'This will limit the function to executing only if the *user* enters a time in
Col A
If Not Intersect(Target, AOI) Is Nothing Then
Application.EnableEvents = False
For Each c In Target
If Not Intersect(Target, c) Is Nothing Then
With c
If VarType(.Value) = vbDate Or VarType(.Value) = vbDouble Then
.Value = Application.WorksheetFunction.Round(.Value / _
TimeSerial(0, 15, 0), 0) * TimeSerial(0, 15, 0)
End If
End With
End If
Next c
End If
Application.EnableEvents = True
End Sub
============================
Be aware that if an error causes this routine to "crash" in the middle of
execution, it may exit without resetting EnableEvents to TRUE. So you may need
to do that manually.
--ron
