Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
Dim lrm As Long
Dim manlist As Worksheet
Dim mylookup As Worksheet
Set mylookup = Sheets("Sheet1")
Set manlist = Sheets("Sheet2")
lr = mylookup.Cells(Rows.Count, 1).End(xlUp).Row
lrm = manlist.Cells(Rows.Count, 1).End(xlUp).Row
If Not Intersect(Target, Range("A2")) Is Nothing Then
Application.EnableEvents = False
If lr <= 2 Then lr = 3
mylookup.Range("B2").ClearContents
mylookup.Range("A3:B" & lr).ClearContents
If mylookup.Range("A2") = "" Then
Application.EnableEvents = True
Exit Sub
End If
mylookup.Range("B2") = Application.Index(manlist.Range("B2:B" & lrm), Application.Match(mylookup.Range("A2"), manlist.Range("A2:A" & lrm), 0))
Application.EnableEvents = True
Exit Sub
End If
If Not Intersect(Target, Range("B2")) Is Nothing Then
Application.EnableEvents = False
If lr <= 2 Then lr = 3
mylookup.Range("A2").ClearContents
mylookup.Range("A3:B" & lr).ClearContents
For x = 2 To lrm
lr = mylookup.Cells(Rows.Count, 1).End(xlUp).Row
If manlist.Cells(x, 2) = mylookup.Range("B2") Then
mylookup.Cells(lr + 1, 1) = manlist.Cells(x, 1)
End If
Next x
Application.EnableEvents = True
End If
End Sub