Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub   'выделено больше одной ячейки
   If Not Intersect(Target, Range("A2:B100")) Is Nothing Then
    Dim FoundName As Range
    Dim FoundKod As Range
      If Target.Column = 1 Then
          Application.EnableEvents = False
        With Sheets("Лист1")
          Set FoundName = .Columns(1).Find(Target, , xlValues, xlWhole)
          If Not FoundName Is Nothing Then
            Target.Offset(, 1) = .Cells(FoundName.Row, 2)
          End If
        End With
          Application.EnableEvents = True
      Else
          Application.EnableEvents = False
        With Sheets("Лист1")
          Set FoundKod = .Columns(2).Find(Target, , xlValues, xlWhole)
          If Not FoundKod Is Nothing Then
            Target.Offset(, -1) = .Cells(FoundKod.Row, 1)
          End If
        End With
          Application.EnableEvents = True
      End If
   End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count > 1 Then Exit Sub
   If Target.Column > 2 Then Exit Sub
   With Sheets("Лист1")
       For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
           If .Cells(i, 1) = Target Then Cells(Target.Row, 2) = .Cells(i, 2): Exit Sub
           If .Cells(i, 2) = Target Then Cells(Target.Row, 1) = .Cells(i, 1): Exit Sub
       Next
   End With
End Sub