- 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
- ---------------------------------------------
Recent Pastes