suche von unten

Private Sub Worksheet_Change(ByVal Target As Range)
Dim varSuchen, rngSuchen As Range, wksRef As Worksheet
If Target.Row > 1 And Target.Column = 4 And Target.Cells.Count = 1 Then
Set wksRef = Worksheets("erl_L1")
varSuchen = Target.Value
Application.EnableEvents = False
If Target.Value = "" Then
Target.Offset(0, 2).ClearContents
Else
With wksRef
Set rngSuchen = .Columns(3).Find(what:=varSuchen, LookIn:=xlValues, Lookat:=xlWhole)
If rngSuchen Is Nothing Then
MsgBox "Nummer " & varSuchen & " in Referenzliste nicht gefunden!"
Else
Target.Offset(0, 2).Value = rngSuchen.Offset(0, 2).Value
End If
End With
End If
Application.EnableEvents = True
End If
End Sub

Gruss
Baumpaul