http://www.vbaexpress.com/kb/getarticle.php?kb_id=75 1. Open the workbook in which you wish to use the code. 2. Right-click the sheet tab of the sheet on which you wish the code to run, and choose View Code. 3. Copy the code above and paste it into the worksheet module window at the right of the Visual Basic Editor (VBE). 4. Redefine the range where the code should work on by adjusting the the line with intersect --------------------------------------------- Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub 'Define the range where you want the code to work (our example is "C:G"). 'Change within the " marks If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub On Error GoTo errHandler: With Target If IsNumeric(.Value) Then Application.EnableEvents = False Select Case .Value Case 0 .NumberFormat = "hh:mm" Case 1 To 99 .Value = TimeSerial(0, .Value, 0) .NumberFormat = "hh:mm" Case 100 To 2399 .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0) .NumberFormat = "hh:mm" Case 10000 To 235959 .Value = TimeSerial(Int(.Value / 10000), _ Int((.Value Mod 10000) / 100), .Value Mod 100) .NumberFormat = "hh:mm:ss" Case 240000 To 245959 .Value = TimeSerial(0, Int((.Value Mod 10000) / 100), .Value Mod 100) .NumberFormat = "hh:mm:ss" Case Else End Select End If End With errHandler: Application.EnableEvents = True End Sub ---------------------------------------------