Automatically inserting Colon (:) in multiple columns under the Options Explicit

Question

I was looking for a code to automatically insert the ':' (colon) into the columns R and S, W and X, and found code that I thought I could customise to my needs, but I am facing two issues:

1. The code works in R and S, but also need the code to run in columns W and X as well

2. I get an error:

   Variable not Defined - stopping at TLen and I guess it will also stop at TimeV

The programmer doesn't use the Option Explicit, (it works OK without Option Explicit). But all my code is always with Option Explicit, but I'm not sure how to write the Dim for the two variables.

This code is in a specific worksheet, in the Worksheet_Change sub, where I have other code for other things, like the timestamp when people make a selection from column B, it will automatically populate when a selection is made in column B.

I have tried the colon code in another workbook, without the Option Explicit and it works without giving errors.

The source of the code came from

Excel VBA tips n tricks #12 no more colons when typing time of day, type 123 instead of 01colon23 AM

I've adapted the code to reference columns R and S in the code below.

Private Sub Worksheet_Change(ByVal Target As Range)

    ' This code will ADD the COLON for TIME automatically
    ' The code is from: https://www.youtube.com/watch?v=ATxaNbTV2d0 (Excel is Fun -
    ' Excel VBA Tips n Tricks #12 NO MORE COLONS When Typing Time of Day, Type 123 instead of 01colon23 AM

    ' To avoid an error if you select more than 1 cell, this next line of code will exit the sub

    If Selection.Count > 1 Then
        Exit Sub
    End If

    If Not Intersect(Range("R4:S1200"), Target) Is Nothing Then

        TLen = Len(Target)
        [![Layout of Worksheet and sample of the columns that need automatic insertion of colons ][1]][1]
        If TLen = 1 Then
            TimeV = TimeValue(Target & ":00")

        ElseIf TLen = 2 Then
            TimeV = TimeValue(Target & ":00")

        ElseIf TLen = 3 Then
            TimeV = TimeValue(Left(Target, 1) & ":" & Right(Target, 2))

        ElseIf TLen = 4 Then
            TimeV = TimeValue(Left(Target, 2) & ":" & Right(Target, 2))

        ElseIf TLen > 4 Then
            'Do nothing

        End If

        'Target.NumberFormat = "HH:MM"
        Application.EnableEvents = False

        Target = TimeV
        Application.EnableEvents = True

    End If
End Sub

Answer 1

Expand the range of the Intersect Intersect(Range("R:S,W:X"),Target).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(Target) = False Then
        MsgBox Target & " is not a number", vbExclamation
        Exit Sub
    ElseIf Intersect(Range("R:S,W:X"), Target) Is Nothing Then
        Exit Sub
    End If
    
    Dim n As Long
    n = Len(Target)
    If n >= 1 And n <= 4 Then
        Application.EnableEvents = False
        Target.NumberFormat = "hh:mm"
        If n <= 2 Then
            Target.Value2 = TimeSerial(Target, 0, 0)
        Else
            Target.Value2 = TimeSerial(Int(Target / 100), Target Mod 100, 0)
        End If
        Application.EnableEvents = True
     End If
End Sub