Soccerstar,

Instead of the change event, try a bulk conversion. Select all the cells
you need to convert, and run the sub below.

HTH,
Bernie
MS Excel MVP

Sub ConvertToTimes()
Dim myCell As Range
Dim TimeStr As String

Application.EnableEvents = False
On Error GoTo Invalid
For Each myCell In Selection
With myCell
If myCell.Value < "" Then
If Not .HasFormula Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "0" & .Value & ":00"
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = .Value & ":00"
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & _
Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & _
Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
..Value = TimeValue(TimeStr)
End If
End If
End With
Invalid:
Next myCell
Application.EnableEvents = True
End Sub


"soccerstr1 " wrote in message
...
i have a spreadsheet with different arrival and departure times for an
airline. the times are entered in military time, and i was wondering if
there is a way to automatically convert the military time without a
colon into military time with a colon, or normal time with a colon.
also, is it possible to do this when data is pasted into a cell? i have
tried using the following vba script, but i have to double click on
each cell individually.