Combine Time from Column Range + Todays Date using VBA on Worksheet Change

[TheRobRush]

Would like to make a VBA private sub that runs a column of data on change and combines in another column the time from the cell + current DAY + AM/PM (value from a matching cell in another column)
If it makes it easier it can also pull date from a third column.

code below is what I started with. It gives screenshot results.

no matter how I try to change it I keep getting type mismatch if I try to add more than current RNG.Value into output

Anyone got time to help?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("C:C"), Target)
xOffsetColumn = 6
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Rng.Value
            
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If



End Sub

https://1drv.ms/u/s!AiJyNsDWxYqVt2w16Imk-U0FLvhd

 

[TheRobRush]

Addition: already know how to do this with just in cell formulas, but the file size in file its used ends up being 5mb which makes it open VERY slowly. Doing it in vba will cut out 1/3 if combining 3 columns, 2/3 if able to do it only using the two cells and today() so trying to cut the size down by using vba.

 

[TheRobRush]

Still having trouble with this one, if anyone has time to give it a look. Thank you in advance!

 

[TheRobRush]

Made some progress, want to leave it here in case it helps someone in the future. The following will combine the time from one cell and the date from another when a change is made in the worksheet. Will work for what I wanted, took some trial and error.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Dim i As Integer
 i = 2
Do While (i < 100 And Cells(i, 1) <> "")
     Cells(i, 11) = Cells(i, 3) + Cells(i, 5)
     i = i + 1
Loop

    
Range("k:k").NumberFormat = "m/d/yy h:mm AM/PM;@"

Application.EnableEvents = True
End Sub