For Schleife

Hallo zusammen,
ich habe ein Frage.
Wie kann ich folgenden Code mit einer For Schleife verkleiner?
If Target.Row = 5 Then
Select Case Cells(5, Target.Column)
Case Is = "u"
Cells(4, Target.Column).Interior.ColorIndex = 5
Case Is = "k"
Cells(4, Target.Column).Interior.ColorIndex = 6
Case Is = "g"
Cells(4, Target.Column).Interior.ColorIndex = 4
Case Is = "d"
Cells(4, Target.Column).Interior.ColorIndex = 13
Case Is = "s"
Cells(4, Target.Column).Interior.ColorIndex = 34
Case Is = ""
Cells(4, Target.Column).Interior.ColorIndex = xlNone
End Select
End If
If Target.Row = 7 Then
Select Case Cells(7, Target.Column)
Case Is = "u"
Cells(6, Target.Column).Interior.ColorIndex = 5
Case Is = "k"
Cells(6, Target.Column).Interior.ColorIndex = 6
Case Is = "g"
Cells(6, Target.Column).Interior.ColorIndex = 4
Case Is = "d"
Cells(6, Target.Column).Interior.ColorIndex = 13
Case Is = "s"
Cells(6, Target.Column).Interior.ColorIndex = 34
Case Is = ""
Cells(6, Target.Column).Interior.ColorIndex = 15
End Select
End If
If Target.Row = 9 Then
Select Case Cells(9, Target.Column)
Case Is = "u"
Cells(8, Target.Column).Interior.ColorIndex = 5
Case Is = "k"
Cells(8, Target.Column).Interior.ColorIndex = 6
Case Is = "g"
Cells(8, Target.Column).Interior.ColorIndex = 4
Case Is = "d"
Cells(8, Target.Column).Interior.ColorIndex = 13
Case Is = "s"
Cells(8, Target.Column).Interior.ColorIndex = 34
Case Is = ""
Cells(8, Target.Column).Interior.ColorIndex = xlNone
End Select
End If
If Target.Row = 11 Then
Select Case Cells(11, Target.Column)
Case Is = "u"
Cells(10, Target.Column).Interior.ColorIndex = 5
Case Is = "k"
Cells(10, Target.Column).Interior.ColorIndex = 6
Case Is = "g"
Cells(10, Target.Column).Interior.ColorIndex = 4
Case Is = "d"
Cells(10, Target.Column).Interior.ColorIndex = 13
Case Is = "s"
Cells(10, Target.Column).Interior.ColorIndex = 34
Case Is = ""
Cells(10, Target.Column).Interior.ColorIndex = 15
End Select
End If
Vielen Dank sachonmal für Eure Antwort
Frank

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: For Schleife

14.03.2007 13:43:08

Holger

AW: For Schleife

14.03.2007 13:51:44

Rudi

Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer Dim arrColorIndex, arrContents arrColorIndex = Array(5, 6, 4, 13, 34, -4142) arrContents = Array("u", "k", "g", "d", "s", "") Select Case Target.Row Case 5, 7, 9, 11 For i = 0 To UBound(arrColorIndex) If Target = arrContents(i) Then Target.Offset(-1, 0).Interior.ColorIndex = arrColorIndex(i) + (Target = "" And (Target.Row = 7 Or Target.Row = 11)) * -4157 Exit For End If Next i End Select End Sub

Beliebteste Forumthreads (12 Monate)

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread