완성

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range
Dim lines() As String
Dim lastLine As String
Dim afterCircle As String

Set rng = Intersect(Target, Me.Columns("K"))
If rng Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each cell In rng
    
    ' K열 내용 중 "물류24hr" 있으면 H열에 Y, 없으면 N
    If InStr(1, cell.Value, "물류24hr", vbTextCompare) > 0 Then
        Me.Cells(cell.Row, "H").Value = "Y"
    Else
        Me.Cells(cell.Row, "H").Value = "N"
    End If
    
    If cell.Value <> "" Then
        
        lines = Split(cell.Value, vbLf)
        lastLine = Trim(lines(UBound(lines)))
        
        If Left(lastLine, 1) = "●" Then
            
            afterCircle = Trim(Mid(lastLine, 2))
            
            ' ●(시간)은 무시, ● 뒤에 다른 내용이면 N열에 현재 날짜+시간
            If afterCircle <> "" And afterCircle <> "(시간)" Then
                Me.Cells(cell.Row, "N").Value = Now
                Me.Cells(cell.Row, "N").NumberFormat = "yyyy-mm-dd hh:mm:ss"
                Me.Columns("N").AutoFit
            Else
                Me.Cells(cell.Row, "N").ClearContents
            End If
            
        Else
            Me.Cells(cell.Row, "N").ClearContents
        End If
        
    Else
        Me.Cells(cell.Row, "N").ClearContents
    End If
    
Next cell

Application.EnableEvents = True

End Sub