Makrobefehl verkürzen

Hallo...
kann man untenstehenden Befehl kürzer machen?
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Key." Then
With Zelle_in_der_Schleife.Offset(0, 0).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Key." Then
With Zelle_in_der_Schleife.Offset(0, 1).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Key." Then
With Zelle_in_der_Schleife.Offset(0, 2).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Key." Then
With Zelle_in_der_Schleife.Offset(-1, 0).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Key." Then
With Zelle_in_der_Schleife.Offset(-1, 1).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Key." Then
With Zelle_in_der_Schleife.Offset(-1, 2).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Blfl." Then
With Zelle_in_der_Schleife.Offset(0, 0).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Blfl." Then
With Zelle_in_der_Schleife.Offset(0, 1).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Blfl." Then
With Zelle_in_der_Schleife.Offset(0, 2).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Blfl." Then
With Zelle_in_der_Schleife.Offset(-1, 0).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Blfl." Then
With Zelle_in_der_Schleife.Offset(-1, 1).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
Next
For Each Zelle_in_der_Schleife In Range("A1:O35")
If Zelle_in_der_Schleife.Value = "Blfl." Then
With Zelle_in_der_Schleife.Offset(-1, 2).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
Next
Wiedermal besten Dank für Eure Hilfe
MfG
Karsten

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub test() Dim Zelle_in_der_Schleife As Range For Each Zelle_in_der_Schleife In Range("A1:O35") If Zelle_in_der_Schleife.Value = "Blfl." Or Zelle_in_der_Schleife.Value = "Key." Then With Range(Zelle_in_der_Schleife.Offset(-1, 0), Zelle_in_der_Schleife.Offset(0, 2)).Interior .ColorIndex = 34 .Pattern = xlSolid End With End If Next End Sub

Sub test() Dim Zelle_in_der_Schleife As Range For Each Zelle_in_der_Schleife In Range("A1:O35") If Zelle_in_der_Schleife.Value = "Key." Then With Range(Zelle_in_der_Schleife.Offset(-1, 0), Zelle_in_der_Schleife.Offset(0, 2)).Interior .ColorIndex = 36 .Pattern = xlSolid End With End If If Zelle_in_der_Schleife.Value = "Blfl." Then With Range(Zelle_in_der_Schleife.Offset(-1, 0), Zelle_in_der_Schleife.Offset(0, 2)).Interior .ColorIndex = 34 .Pattern = xlSolid End With End If Next End Sub

Hio...
Mal abgesehen dass der Code bei mir einfach mal garnix macht.
Tabelle1:

Option ExplicitSub test()

    Dim Zelle_in_der_Schleife As Range

    For Each Zelle_in_der_Schleife In Range("A1:O35")

        If Zelle_in_der_Schleife.Value = "Key." Then

            With Range(Zelle_in_der_Schleife.Offset(0, 0), Zelle_in_der_Schleife.Offset(0, 2)).Interior

                .ColorIndex = 36

                .Pattern = xlSolid

            End With

            With Range(Zelle_in_der_Schleife.Offset(-1, 0), Zelle_in_der_Schleife.Offset(-1, 2)).Interior

                .ColorIndex = 36

                .Pattern = xlSolid

            End With

        End If

        If Zelle_in_der_Schleife.Value = "Blfl." Then

            With Range(Zelle_in_der_Schleife.Offset(0, 0), Zelle_in_der_Schleife.Offset(0, 2)).Interior

                .ColorIndex = 34

                .Pattern = xlSolid

            End With

            With Range(Zelle_in_der_Schleife.Offset(-1, 0), Zelle_in_der_Schleife.Offset(-1, 2)).Interior

                .ColorIndex = 34

                .Pattern = xlSolid

            End With

        End If

    Next

End Sub

Rückmeldung nicht vergessen...

c0bRa
I hacked 127.0.0.1

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