Option Explicit
Private Sub ActiveWorksheet_Change(ByVal Target As Range)
If Target = "B70" Then
Call Kunde_Lieferant
End If
End Sub
Sub Kunde_Lieferant()
If Range("B70").Value = "5" Then
Range("C8").Select
ActiveCell.FormulaR1C1 = "Lieferanten Nr.:"
Range("F8").Select
Selection.Copy
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A8").Select
Selection.Copy
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Cells(70, 2).Value = "5"
Range("D8").Select
ElseIf Range("B70").Value = "4" Then
Range("C8").Select
ActiveCell.FormulaR1C1 = "Kunden Nr.:"
Range("F8").Select
Selection.Copy
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A8").Select
Selection.Copy
Range("C8").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Cells(70, 2).Value = "4"
Range("D8").Select
Else
Range("C8:D8").Select
Selection.Clear
ActiveSheet.Cells(70, 2).Value = "0"
End Sub |