For Schleife

Forumthread: For Schleife

For Schleife

14.03.2007 13:30:00

Frank

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

2
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

AW: For Schleife

14.03.2007 13:43:08

Holger

Hallo Frank,
ohne Schleife z.B. mit
If (Target.Row = 5) or (Target.Row = 7) or (Target.Row = 9) or (Target.Row = 11) or Then
Select Case Cells(Target.Row , 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
Holger

AW: For Schleife

14.03.2007 13:51:44

Rudi

Hallo,


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

Gruß
Rudi
Eine Kuh macht Muh, viele Kühe machen Mühe

Beliebteste Forumthreads (12 Monate)

Forumthread: For Schleife

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

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread