Frage von slow1, 56

MS Excel,VBA - Wie kann ich eine until Scheilfe mit 2 Bedingungen erstellen?

Hallo,

ich stehe vor dem Problem, das ich eine Schleife in Excel einbinden muss. Wenn ich das richtig verstanden habe ist dies nur mit VBA möglich, leider habe ich keinerlei Kenntnisse in VBA…ich hoffe ihr könnt mir weiterhelfen.

Ich habe die Datei so vereinfacht, dass die Formel so einfach wie möglich bleibt. Folgendes Problem, Ich habe 3 Spalten:

Wenn in Spalte A die Zahl „2“ vorkommt soll eine rote Markierung in Spalte C (in der gleichen Zeile) erfolgen.

Die rote Markierung in Spalte C soll erst dann aufhören wenn in Spalte B die Zahl 2 enthält

….danach soll es wieder von vorne losgehen, die Schleife soll über die ganze Spalte hinweg laufen. Zur Veranschaulichung – so soll das Endergebnis aussehen (sry für die furchbare Formatierung)

0 0

2 0 rot

0 0 rot

0 0 rot

0 0 rot

0 2 rot

0 0

2 0 rot

0 2 rot

0 0

0 0

0 2

0 0

0 2

0 0

0 0

2 0

0 0

0 2 rot

Expertenantwort
von Oubyi, Community-Experte für Excel, 56

Da ich die anderen Antworten noch nicht sehen konnte, gebe ich auch mal "meine Senf dazu":

Mach mal einen Rechtsklick auf den Tabellenreiter (unten, da wo z.B. Tabelle1 steht) und gehe dann auf "Code anzeigen...".In das große Fenster des VBA-Editors, der sich dann öffnet kopierst Du folgenden Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B100")) Is Nothing Then
Dim Bereich As Range
Dim Zelle As Range
Dim Rot As Boolean
Rot = False
Set Bereich = Range("A1:A100")
For Each Zelle In Bereich
If Zelle.Value = 2 Then Rot = True
If Rot = True Then Zelle.Offset(0, 2).Interior.ColorIndex = 3
If Rot = False Then Zelle.Offset(0, 2).Interior.ColorIndex = xlNone
If Zelle.Offset(0, 1).Value = 2 Then Rot = False
Next Zelle
End If
End Sub

Die Datei musst Du danach aber als .xlsm speichern, und Makros zulassen.
Der Code wird immer automatisch ausgeführt - und somit die Zellfarben neu berechnet-, wenn Du im Bereich A1:B100 einen Zellwert änderst.

Du solltest aber auf jeden Fall auch die Lösung von Suboptimierer mit Hilfsspalte und Bedingter Formatierung in Erwägung ziehen.


Antwort
von PWolff, 51

Mir ist nicht ganz klar, ob die Markierung neben der jeweiligen 2 beginnen bzw aufhören soll oder erst in der folgenden Zeile.

Was ist mit der drittletzten Zeile?

Ich würde vermutlich etwas in dieser Art machen:

Dim zeile As Integer
Dim markieren As Boolean
markieren = False
For zeile = 1 to 32766 '1 weniger als max. Integer (32767), da es sonst zum Überlauf kommt
 If Cells(zeile, 1).Value = 2 Then markieren = True
 If markieren Then
 Cells(zeile, 3).Interior.ColorIndex = 3 'rot
Else
 Cells(zeile, 3).Interior.ColorIndex = xlColorIndexNone
 End If
 If Cells(zeile, 2).Value = 2 Then markieren = False
Next zeile '= 1 to 32766
Kommentar von PWolff ,

Es geht auch mit bedingter Formatierung (wenn auch vermutlich nicht ohne Makro):

Unter "Format" -> "Bedingte Formatierung" auswählen

bei "Bedingung" auswählen: "Formel ist" und eintragen:

=VERGLEICHRÜCKWÄRTS(2;A$1:A1)>VERGLEICHRÜCKWÄRTS(2;B$1:B1)

und bei "Format..." auswählen: "Muster" -> Farbe rot

Wir brauchen aber noch die Funktion VERGLEICHRÜCKWÄRTS.

Dazu in einem Codemodul eintragen:

Function VERGLEICHRÜCKWÄRTS(Suchkriterium, Suchmatrix As Range) As integer
'Vergleiche VERGLEICH; hier ist Vergleichstyp immer 0
Dim retVal As Integer
Dim cell As Range, cell Index As Integer
retVal = -1
cellIndex = 0
For Each cell In Suchmatrix
cellIndex = cellIndex + 1
If cell.Value Like Suchkriterium Then retVal = cellIndex
Next
VERGLEICHRÜCKWÄRTS = retVal
End Function

(Ich wüsste keine Funktion, die rückwärts suchen kann)

Kommentar von PWolff ,

Suboptimierer hat Recht - es geht auch ganz ohne VBA.

Wir brauchen allerdings eine Hilfsspalte. Nehmen wir an, wir nehmen Spalte K dafür.

Formel für K1:

=UND($B1<>2;ODER($A1=2;FALSCH))

Formel für K2:

=UND($B2<>2;ODER($A2=2;K1))

Die Formel von K2 in alle benötigten Zeilen nach unten kopieren/ziehen.

Dann kann einfach Spalte K als Bedingung für die Formatierung verwendet werden.

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 39

Du musst das nicht unbedingt in VBA lösen. Das geht auch mit reinem Excel.

Leg eine Hilfsspalte an (im Screenshot Spalte C) und definiere eine bedingte Formatierung darauf.

Die vorletzte und drittletzte Zeile in deinem Beispiel müssten übrigens nach deiner Logik rot sein.

Kommentar von Suboptimierer ,

Eine Prozedur dazu könnte so aussehen:

Sub RotSehen()
  Dim i As Integer
  Dim rot As Long: rot = RGB(255, 0, 0)

  If Cells(1, 1).Value = 2 Then
    Cells(1, 3).Interior.Color = rot
  Else
    Cells(1, 3).Interior.Color = xlNone
  End If
  
  For i = 2 To 19
    If (Cells(i - 1, 3).Interior.Color = rot And Cells(i - 1, 2).Value <> 2) Or _
        Cells(i, 1).Value = 2 Then
      Cells(i, 3).Interior.Color = rot
    Else
      Cells(i, 3).Interior.Color = xlNone
    End If
  Next
End Sub
Kommentar von Suboptimierer ,
  • Sehe gerade, dass ich für rot auch die VB-Konstante vbRed hätte nehmen können.
  • Die Schleifenbedingung kannst du noch variabel gestalten, zum Beispiel while(Cells(i,1).Value <> "")
Antwort
von BeMaDataBoFan, 44

Du hast die Bedingung doch schon richtig beschrieben.

In Excel gibt es die Funktion "Bedingte Formatierung". Mit dieser Funktion und Deinen Bedingungen hast Du, was Du willst, erreicht.

Geht natürlich auch mit VBA (Wenn,dann,sonst = If,then,else), aber warum so kompliziert und das Rad nochmal erfinden?

Die bedingte Formatierung in Excel wäre die richtige Lösung für Dich, denke ich. Ausprobieren! Wenn ..., dann Rot, sonst ... ! So funktioniert diese Funktion, ganz einfach!

Formel kopieren in soviel Zeilen, wie nötig / gebraucht ... nicht vergessen!

Kommentar von BeMaDataBoFan ,

Danke fürs Dankeschön! ... war doch auch 'ne HILFREICHE ANTWORT ? Gruß, Bernd. ;-) :-) :)

Antwort
von slow1, 29

Danke erstmal an alle für die Antworten.

Ich muss mich entschuldigen...die Frage hatte einen Fehler und wurde deshalb unverständlich.

Es ist so, dass mir 2 Spalten (A und B) vorliegen, das Ergebnis soll in Spalte C eingetragen werden

Das Ergebnis der Spalte C soll eine  rote Markierung sein..

Die rote Markierung in Spalte C soll beginnen sobald in Spalte A der Wert 2 vorkommt und enden wenn in Spalte B der Wert 2 vorkommt.

Das ganze soll über die ganze Spalte hinweg laufen (File hat ca. 40k Zeilen)

Kommentar von Oubyi ,

Und wieso helfen unsere Lösungen Dir dabei nicht?
Ich zumindest hatte es genau so verstanden und mein Code sollte das eigentlich entsprechend lösen.
Du musst den Bereich nur anpassen, also z.B. ALLE "A1:A100" durch "A1:A10000" ersetzen.
Was macht er falsch?

Antwort
von slow1, 15

Ich melde mich kurz zurück, da ich erst jetzt mit Excel "herumspielen" konnte. 

Beide Varianten funktionieren! Sowohl die Bedingte Formatierung (genial einfach!) als auch der VBA Code!.

Danke nochmal an alle für die tolle Hilfe!!

Kommentar von maximilianus7 ,

welche der beiden verfahren für die bedingte formatierung hast du jetzt verwendet? das mit dem VERGLEICHRÜCKWÄRTS oder das mit den K1/K2-Formeln?

(K1/K2 hat bei mir nicht funktioniert)

Antwort
von xGlumi, 18
Sub Formatieren()
Dim zelleA As Range
Dim zelleB As Range
Dim zelleC As Range
Dim makeRed, last As Boolean
makeRed = False
For Each zelleA In ActiveSheet.Range("A:A").Cells
Set zelleB = ActiveSheet.Range("B" & zelleA.Row)
If zelleA.Value = "2" Then
makeRed = True
ActiveSheet.Range("C" & zelleA.Row).Interior.ColorIndex = 3
ElseIf zelleB.Value = "2" Then
ActiveSheet.Range("C" & zelleA.Row).Interior.ColorIndex = 3
makeRed = False
last = True
ElseIf zelleA.Value = "" And zelleB.Value = "" Then
Exit Sub
End If
If makeRed = True Or last = True Then
ActiveSheet.Range("C" & zelleA.Row).Interior.ColorIndex = 3
last = False
Else
ActiveSheet.Range("C" & zelleA.Row).Interior.ColorIndex = 0
End If
Next zelleA
End Sub


Man sollte hier (wenn man schon Objekte nutzen kann) mit einer For-Each Schleife arbeiten..

MFG xGlumi


Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten