Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range) If Cells(2, 25) = "x" Then Sheets("Test").Tab.ColorIndex = 4 If Cells(2, 25) = "" Then Sheets("Test").Tab.ColorIndex = 23 Dim var As Variant If Target.Column 5 Then Exit Sub If Target.Cells.Count > 5 Then Exit Sub Application.EnableEvents = False On Error GoTo ERRORHANDLER With Worksheets("Adressen") If Not IsEmpty(Target) Then var = Application.Match(Target.Value, .Columns(1), 0) If Not IsError(var) Then Target.Offset(0, 1).Value = .Cells(var, 2).Value Target.Offset(0, 2).Value = .Cells(var, 14).Value Target.Offset(0, 3).Value = .Cells(var, 5).Value End If Else Range(Target, Target.Offset(0, 7)).ClearContents End If End With ERRORHANDLER: Application.EnableEvents = True End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim var As Variant If Cells(2, 25) = "x" Then Sheets("Test").Tab.ColorIndex = 4 If Cells(2, 25) = "" Then Sheets("Test").Tab.ColorIndex = 23 If Target.Column 5 Or Target.Column 9 Then Exit Sub If Target.Cells.Count > 5 Then Exit Sub Select Case Len(Target.Text) Case Is > 10: Target.HorizontalAlignment = xlLeft End Select Application.EnableEvents = False On Error GoTo ERRORHANDLER With Worksheets("Adressen") If Not IsEmpty(Target) Then var = Application.Match(Target.Value, .Columns(1), 0) If Not IsError(var) Then Target.Offset(0, 1).Value = .Cells(var, 2).Value Target.Offset(0, 2).Value = .Cells(var, 14).Value Target.Offset(0, 3).Value = .Cells(var, 5).Value End If Else Range(Target, Target.Offset(0, 7)).ClearContents End If End With ERRORHANDLER: Application.EnableEvents = True End Sub

Hallo,
noch 'ne Variante:


Private Sub Worksheet_Change(ByVal Target As Range)
Call EINS(Target)
Call ZWEI(Target)
End Sub


Private Sub EINS(Target As Range)
Dim var As Variant
If Cells(2, 25) = "x" Then Sheets("Test").Tab.ColorIndex = 4
If Cells(2, 25) = "" Then Sheets("Test").Tab.ColorIndex = 23
If Target.Column  5 Then Exit Sub
If Target.Cells.Count > 5 Then Exit Sub
Application.EnableEvents = False
On Error GoTo ERRORHANDLER
With Worksheets("Adressen")
If Not IsEmpty(Target) Then
var = Application.Match(Target.Value, .Columns(1), 0)
If Not IsError(var) Then
Target.Offset(0, 1).Value = .Cells(var, 2).Value
Target.Offset(0, 2).Value = .Cells(var, 14).Value
Target.Offset(0, 3).Value = .Cells(var, 5).Value
End If
Else
Range(Target, Target.Offset(0, 7)).ClearContents
End If
End With
ERRORHANDLER:
Application.EnableEvents = True
End Sub


Private Sub ZWEI(Target As Range)
If Target.Column  9 Then Exit Sub
Select Case Len(Target.Text)
Case Is > 10: Target.HorizontalAlignment = xlLeft
End Select
End Sub

Gruß Sigi

Beliebteste Forumthreads (12 Monate)

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread