Hello - there many quesitons that very nearly provide the answer to this, and think I've tried them all....I'm probably missing something as I'm putting the pieces together. The following code works just find on a range in an excel table I have (adding colon's to times entered), but I have 5 seperate tables, that I need this to apply to. I have tried writing 5 sub procedures and calling them based on condition, but thats not working - probably my syntax.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim TimeStr As String

    If Application.Intersect(Target, Range("TableResp[Time]")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If IsDate(Target.Text) And Target.Value < 1 Then Exit Sub

    On Error GoTo EndMacro

    Application.EnableEvents = False
    With Target
        If .HasFormula = False Then
            Select Case Len(.Value)
                Case 1    ' e.g., 1 = 00:01 AM
                    TimeStr = "00:0" & .Value
                Case 2    ' e.g., 12 = 00:12 AM
                    TimeStr = "00:" & .Value
                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 With
    Application.EnableEvents = True
    Exit Sub
EndMacro:
    MsgBox "You did not enter a valid time"
    Target.Value = ""
    Application.EnableEvents = True
End Sub
thanks