FormatConditions in VBA: Bedingte Formatierung per Makro

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.Deleteentfernt alle Regeln dieses Bereichs. Tun Sie das vor dem Neuanlegen, sonst entstehen Duplikate. - Anzahl prüfen:
Range("A1").FormatConditions.Countgibt die Zahl der Regeln zurück. - Priorität: Mit
.SetFirstPrioritysetzen Sie eine Regel an die erste Stelle, mit.StopIfTrue = Trueverhindern 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:
- Löschen Sie vorhandene Regeln mit
Range("C2:C100").FormatConditions.Delete. - Legen Sie eine Regel vom Typ
xlExpressionmit der Formel=C2<HEUTE()an und setzen Sie die Füllfarbe auf Rot. - Fügen Sie eine zweite Regel mit
=UND(C2>=HEUTE();C2<=HEUTE()+7)hinzu und setzen Sie Gelb. - 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.




