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

Answer 2

I understand that you are 'stretching & teaching' me to work things out for myself, and it is appreciative (and I definitely have learned how to see the type (1.)). But in this instance, the 'Type' is coming as Variant/Date, even though it is meant to be time (maybe I am misunderstanding the syntax). – TheShyButterfly

You did well! Yes, that is one way to find the type. The other way is to use the VarType function:

Option Explicit

Sub Sample()
    Dim TimeA

    TimeA = TimeValue("01:00 PM")

    MsgBox VarType(TimeA)
End Sub

This will give you 7 which is vbDate.

You can also store time as Variant and Double as shown below.

Option Explicit

Sub Sample()
    Dim TimeA As Date
    Dim TimeB As Double
    Dim TimeC As Variant

    TimeA = TimeValue("01:00 PM")
    TimeB = TimeValue("01:00 PM")
    TimeC = TimeValue("01:00 PM")

    MsgBox "Time stored as Date : " & TimeA
    MsgBox "Time stored as Double : " & TimeB
    MsgBox "Time stored as Variant : " & TimeC

    MsgBox "TimeA formated as Date : " & Format(TimeA, "hh:mm:ss AM/PM")
    MsgBox "TimeB formated as Date : " & Format(TimeB, "hh:mm:ss AM/PM")
    MsgBox "TimeC formated as Date : " & Format(TimeC, "hh:mm:ss AM/PM")
End Sub

but without an example how am I to learn, I have obviously exhausted my search on resolving this, but found nothing .. the reason why I posted the question. Thank you for encouraging me to continue solving things on my own :) TheShyButterfly

You can write the range as CDP1802 shown in his post or you can use the Application.Union method (Excel).

For example,

Option Explicit

Sub Sample()
    Dim rngA As Range
    Dim rngB As Range
    Dim rngCombined As Range

    Set rngA = Range("R4:S1200")
    Set rngB = Range("W4:X1200")

    Set rngCombined = Union(rngA, rngB)

    MsgBox rngCombined.Address
End Sub

So in your code it becomes Intersect(rngCombined, Target) Is Nothing.

Also since you are working with Worksheet_Change and Events, I recommend seeing Working with Worksheet_Change.