Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 3 Then Exit Sub
    If Target.CountLarge <> 1 Then Exit Sub
        Call Макрос1(Target)
End Sub
Private Sub Макрос1(Target As Range)
Dim arr_katalog, number_row As Long, lr As Long

With Worksheets("каталог")
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    arr_katalog = .Range(.Cells(1, 1), .Cells(lr, 2))
    number_row = find_in_array(arr_katalog, Target.Value)
    If number_row Then
        .Cells(number_row, 2).Copy
        Target.PasteSpecial xlPasteComments
    End If
End With
End Sub

Private Function find_in_array(arr, what)
Dim i
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 2) = what Then find_in_array = i: Exit Function
    Next i
End Function

Private Sub Worksheet_Change(ByVal Target As Range)