Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Sheets("Test").Unprotect
Dim RaBereich As Range, rngZelle As Range
Set RaBereich = Columns(1)
Set RaBereich = Intersect(RaBereich, Range(Target.Address))
If Not RaBereich Is Nothing Then
For Each rngZelle In RaBereich
If rngZelle.Row > 29 And rngZelle.Row < 43 Then
If rngZelle.Value <> "" Then
Application.EnableEvents = False
If rngZelle.Offset(0, 1).FormulaR1C1 <> "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,5,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,5,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,5,FALSE)))" Then
rngZelle.Offset(0, 1).FormulaR1C1 = "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,5,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,5,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,5,FALSE)))"
End If
If rngZelle.Offset(0, 2).FormulaR1C1 <> "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,6,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,6,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,6,FALSE)))" Then
rngZelle.Offset(0, 2).FormulaR1C1 = "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,6,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,6,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,6,FALSE)))"
End If
If rngZelle.Offset(0, 3).FormulaR1C1 <> "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,9,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,9,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,9,FALSE)))" Then
rngZelle.Offset(0, 3).FormulaR1C1 = "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,9,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,9,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,9,FALSE)))"
End If
If rngZelle.Offset(0, 5).FormulaR1C1 <> "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,2,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,2,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,2,FALSE)))" Then
rngZelle.Offset(0, 5).FormulaR1C1 = "=IF(RC1="""","""",IF(OR(ISBLANK(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,2,FALSE)),ISERROR(VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,2,FALSE))),"""",VLOOKUP(RC1,'C:\Temp\[Materialdaten.xlsm]Materialdaten'!C1:C9,2,FALSE)))"
End If
If rngZelle.Offset(0, 6).FormulaR1C1 <> "=IF(OR(RC[-2]="""",RC[-1]=""""),"""",RC[-2]*RC[-1])" Then
rngZelle.Offset(0, 6).FormulaR1C1 = "=IF(OR(RC[-2]="""",RC[-1]=""""),"""",RC[-2]*RC[-1])"
End If
Application.EnableEvents = True
Else
Application.EnableEvents = False
rngZelle.Offset(0, 4).Value = ""
Application.EnableEvents = True
End If
End If
Next rngZelle
End If
Set RaBereich = Nothing
ThisWorkbook.Sheets("Test").Protect
End Sub