Time Colon VBA

[Brian from Maui]

Trying to revise a code to automatically add a colon and decimal point.

Cell is Custom Formatted as [mm]:ss.00

Want to just enter six digits and the colon and decimal point would be added with the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
    If Len(Target) = 6 Then .Value = Left(.Value, 2) & ":" & Mid(.Value, 2) & "." & Right(.Value, 2)
    Application.EnableEvents = True
    End With
End Sub

Obviuosly not not working. Can someone correct this? TIA

 

[Brian from Maui]

When I enter 182200, I get 26236800:00.00

When I enter 182200 and the cell is formatted as [m]:ss.0, I get 182:20.0

Thanks shg

let me play around with this more. It's gotta be me.......

 

[Brian from Maui]

If you are asking how to bulletproof the code I suggested, try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo oops
    For Each t In Target
        t.Value = Format(t.Value, "0\:00\.00")
nexttarget:
    Next
    Application.EnableEvents = True
    Exit Sub
oops:
    Resume nexttarget
End Sub

Thanks, I'll have a play with this also..........

 

[shg]

    If Intersect(Target, Range("timeCells")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo oops
    For Each t In Target

That code assumes that the entirety of Target is in timeCells if any cells are.