Anwendungen

FormatConditions in VBA: Bedingte Formatierung per Makro

Kurz erklärt: Mit Range.FormatConditions.Add legen Sie per VBA bedingte Formatierungen an. Sie wählen den Typ (xlCellValue oder xlExpression), den Vergleich und die Formel und setzen anschließend Farbe oder Schrift. Mit FormatConditions.Delete entfernen Sie alle Regeln wieder.

Bedingte Formatierungen lassen sich in Excel per Hand einrichten. Wer sie aber in vielen Dateien oder dynamisch braucht, spart mit einem Makro Zeit. Das Objekt FormatConditions gehört zu jedem Zellbereich und bildet die Regeln ab.

Eine Regel nach Zellwert anlegen

Das folgende Makro färbt alle Zellen in A1:A20 rot, wenn der Wert größer als 100 ist:

Sub WerteMarkieren()
  With Range("A1:A20").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100")
    .Interior.Color = RGB(255, 199, 206)
    .Font.Color = RGB(156, 0, 6)
  End With
End Sub

Mögliche Operatoren sind unter anderem xlBetween, xlEqual, xlLess und xlNotEqual. Bei xlBetween geben Sie zusätzlich Formula2 an.

Regel mit Formel

Für komplexere Bedingungen verwenden Sie xlExpression. Die Formel bezieht sich auf die erste Zelle des Bereichs:

Range("A2:D50").FormatConditions.Add(Type:=xlExpression, Formula1:="=$D2=""offen""").Interior.Color = RGB(255, 235, 156)

So färbt sich die ganze Zeile gelb, wenn in Spalte D der Wert „offen“ steht. Achten Sie auf das Dollarzeichen vor der Spalte.

Weitere Formatierungsarten

Methode Ergebnis
FormatConditions.Add Zellwert- oder Formelregel
FormatConditions.AddColorScale(3) Farbskala mit drei Farben
FormatConditions.AddDatabar Datenbalken
FormatConditions.AddIconSetCondition Symbolsatz
FormatConditions.Delete Alle Regeln im Bereich löschen

Regeln verwalten

  • Löschen: Range("A1:A20").FormatConditions.Delete entfernt alle Regeln dieses Bereichs. Tun Sie das vor dem Neuanlegen, sonst entstehen Duplikate.
  • Anzahl prüfen: Range("A1").FormatConditions.Count gibt die Zahl der Regeln zurück.
  • Priorität: Mit .SetFirstPriority setzen Sie eine Regel an die erste Stelle, mit .StopIfTrue = True verhindern Sie weitere Auswertungen.
  • Grenze: Pro Bereich sind viele Regeln möglich, zu viele machen die Datei jedoch langsam.

Die manuelle Variante lesen Sie in unserem Beitrag, wie Sie Daten mithilfe der bedingten Formatierung ausblenden. Für automatische Nummerierung per Code siehe fortlaufende Nummerierung mit VBA.

Praxisbeispiel: Ampel für Termine

Angenommen, in Spalte C stehen Fälligkeitsdaten. Mit zwei Regeln färben Sie überfällige Termine rot und Termine der nächsten sieben Tage gelb:

  1. Löschen Sie vorhandene Regeln mit Range("C2:C100").FormatConditions.Delete.
  2. Legen Sie eine Regel vom Typ xlExpression mit der Formel =C2<HEUTE() an und setzen Sie die Füllfarbe auf Rot.
  3. Fügen Sie eine zweite Regel mit =UND(C2>=HEUTE();C2<=HEUTE()+7) hinzu und setzen Sie Gelb.
  4. Prüfen Sie über Start → Bedingte Formatierung → Regeln verwalten, ob die Reihenfolge stimmt.

Weil sich die Formel auf die obere linke Zelle bezieht, wird sie für alle weiteren Zellen automatisch angepasst. Rufen Sie das Makro beim Öffnen der Datei auf, damit die Regeln immer aktuell sind.

Häufige Fragen

Warum erscheint ein Laufzeitfehler bei Formula1?

Meist stimmt die Schreibweise der Formel nicht. Bei FormatConditions wird sie in der lokalen Notation Ihrer Excel-Sprache erwartet, also mit deutschen Funktionsnamen und Semikolon als Trennzeichen. Testen Sie die Formel vorher in einer Zelle.

Wie entferne ich die Formatierung für die ganze Tabelle?

Verwenden Sie Cells.FormatConditions.Delete.

Kann ich eigene Farben nutzen?

Ja, mit RGB(r, g, b) für beliebige Farbwerte.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Schaltfläche "Zurück zum Anfang"