Hi,

Put this in your Worksheet Code Module:


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Application.Intersect(Range("B2:B100"), Target) _
Is Nothing Then GoTo e:

With Target
If .Cells.Count 1 Then GoTo e:
If Not IsNumeric(.Value) Then GoTo e:
If .HasFormula Then GoTo e:
If .Value Like "[!0123456789]" Then GoTo e:

Select Case Len(.Value)
Case 1: .Value = "00:0" & .Value
Case 2: .Value = "00:" & .Value
Case 3, 4: .Value = Left(.Value, 1) & ":" & Right(.Value, 2)
Case Else: .ClearContents
End Select

If .Value = 1 Then .ClearContents
.NumberFormatLocal = "HH:MM"

End With

e:
Application.EnableEvents = True

End Sub


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--