Vba to timestamp

[Pisus]

Hello everyone,
I was trying to figure this out, but couldn't on my own, so there is nothing for me else to do but to turn to the pros.
I have this code which copies a template to the bottom of the active sheet from another sheet if a value is entered in column U,
The problem is that the part which checks if it's a new lot number and the entry timestamp, only works if i enter the "Box code" value in the cell where is should be.
To explain it simply if i enter the code in a specific cell, in column U it looks like this:

Result: Code is near the "box code:", G32 is marked as NEW, I32 has a timestamp, because the code was entered in U32
If i enter the code anywhere in column U it looks like this:

Result: Code is near the "box code:", G6 is not marked NEW, I6 doesn't have a timestamp, but I9 has, because the code was entered in U9.

Sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set r = Target.Cells(1, 1)
    If r.Count > 1 Then Exit Sub
    If Len(r.Value) = 0 Then Exit Sub
    If r.Value = "" Then Exit Sub
    If r.Column <> 21 Then Exit Sub
        
        Application.EnableEvents = False
            Call AddTable(ActiveSheet.Name, Target)
        Application.EnableEvents = True
        
     r.Offset(0, -14).Interior.ColorIndex = 0
    If Not r.Offset(0, -14) = r.Offset(-10, -14) Then
    MsgBox "NEW LOT!!"
    r.Offset(0, -14).Interior.ColorIndex = 4
    End If
    
   With Target
      If .Row <= 2 Then Exit Sub
      Select Case .Column
        Case Cells(1, "I").Column
           Exit Sub
         Case Cells(1, "U").Column
         With .Offset(0, -12)
             .Value = Date
             .NumberFormat = "yyyy/mm/dd"
          End With
      End Select
   End With

End Sub

Module1 code:

Option Explicit

Sub Reset()
    Application.EnableEvents = True
End Sub

Sub AddTable(ByVal TemplateName As String, ByVal Target As Range)
    
    Dim Cell    As Range
    Dim cnt     As Long
    Dim Rng     As Range
    Dim RngBeg  As Range
    Dim RngEnd  As Range
    Dim TblRng  As Range
    Dim Wks     As Worksheet
    

        Set Wks = ThisWorkbook.Worksheets("Templates")
        Set Rng = Wks.Range("W1", Wks.Cells(Rows.Count, "W").End(xlUp))
        
        ' // Find the cell in column "W" matching the Template name.
        Set Cell = Rng.Find(TemplateName, Rng.Cells(Rng.Rows.Count, 1), xlValues, xlPart, xlByRows, xlNext, False, False, False)
    
        ' // Display error message if Template name was not found.
        If Cell Is Nothing Then
            MsgBox TemplateName & " - Template not found", vbExclamation
            Exit Sub
        End If
        
        ' // Move to column "S" across from the Template name.
        Set Cell = Cell.Offset(0, -4)
        
        ' // Count the cells with borders to find the Table rows.
        Do
            cnt = cnt + 1
            If Cell.Offset(cnt - 1, 0).Borders(xlEdgeLeft).LineStyle = xlLineStyleNone Then
                cnt = IIf(cnt = 1, cnt, cnt - 1)
                Exit Do
            End If
        Loop
        
        ' // Table is from columns "A:U" with "cnt" rows.
        Set TblRng = Cell.Offset(0, -18).Resize(cnt, 20)
        
        ' // Look in column "U" of the Active Worksheet for "Box code:"
        Set RngBeg = Target.Offset(0, -1)
        Set RngEnd = RngBeg.EntireColumn.Find("Box code:", , xlFormulas, xlWhole, xlByRows, xlPrevious, False, False, False)
        
        
        If RngEnd Is Nothing Then
            ' // If not found this is the first table on the sheet.
            ' // Start the table in the same row as the Active Cell.
            Set Rng = RngBeg.Offset(0, -19).Resize(cnt, 20)
        Else
            ' // A table already exists.
            ' // Start on the row below the table.
            Set Rng = RngEnd.Offset(cnt, -19).Resize(cnt, 20)
        End If
        
        ' // Copy the Table Template.
        TblRng.Copy
        ' // Paste it to the Active Sheet.
        Rng.PasteSpecial Paste:=xlPasteAll
        
        ' // Move the code entered in column "U" so it is across from "Box Code:"
        If Target.Row <> Rng.Row Then
            Rng.Cells(1, 21) = Target.Value
            Target.Value = Empty
        End If


        ' // Select the cell with the entered code.
        Rng.Cells(1, 21).Select      
End Sub

To put it even more simply:
How to make this:

r.Offset(0, -14).Interior.ColorIndex = 0
    If Not r.Offset(0, -14) = r.Offset(-10, -14) Then
    MsgBox "NEW LOT!!"
    r.Offset(0, -14).Interior.ColorIndex = 4
    End If
With Target
      If .Row <= 2 Then Exit Sub
      Select Case .Column
        Case Cells(1, "I").Column
           Exit Sub
         Case Cells(1, "U").Column
         With .Offset(0, -12)
             .Value = Date
             .NumberFormat = "yyyy/mm/dd"
          End With
      End Select
   End With

End Sub

Work after this :

Application.EnableEvents = False
            Call AddTable(ActiveSheet.Name, Target)
        Application.EnableEvents = True

Any kind of help and tips are really appreciated.

Original post here:Here

 

[Pisus]

Cross-posted Here

 

[Pisus]

Solved here