Bedingte Formatierung in Excel VBA
Wir können eine bedingte Formatierung auf eine Zelle oder einen Zellbereich in Excel anwenden. Ein bedingtes Format ist ein Format, das nur auf Zellen angewendet wird, die bestimmte Kriterien erfüllen, z. B. Werte über einem bestimmten Wert, positive oder negative Werte oder Werte mit einer bestimmten Formel usw. Diese bedingte Formatierung kann auch in der Excel-VBA-Programmierung verwendet werden die ' Format Conditions Collection ' im Makro / der Prozedur.
Formatbedingung wird verwendet, um ein bedingtes Format darzustellen, das durch Aufrufen einer Methode festgelegt werden kann, die eine Variable dieses Typs zurückgibt. Es enthält alle bedingten Formate für einen einzelnen Bereich und kann nur drei Formatbedingungen enthalten.
FormatConditions.Add / Modify / Delete wird in VBA verwendet, um der Sammlung FormatCondition-Objekte hinzuzufügen / zu ändern / zu löschen. Jedes Format wird durch ein FormatCondition-Objekt dargestellt. FormatConditions ist eine Eigenschaft des Range-Objekts. Fügen Sie die folgenden Parameter mit der folgenden Syntax hinzu:
FormatConditions.Add (Typ, Operator, Formel1, Formel2)
Die Syntax zum Hinzufügen von Formeln enthält die folgenden Argumente:
- Typ: Erforderlich, gibt an, ob das bedingte Format auf dem in der Zelle vorhandenen Wert oder einem Ausdruck basiert.
- Operator: Optional, stellt den Operator dar, der mit einem Wert verwendet werden soll, wenn 'Typ' auf dem Zellenwert basiert.
- Formel 1: Optional, repräsentiert den Wert oder Ausdruck, der dem bedingten Format zugeordnet ist.
- Formel 2: Optional, stellt den Wert oder Ausdruck dar, der dem zweiten Teil des bedingten Formats zugeordnet ist, wenn der Parameter: 'Operator' entweder 'xlBetween' oder 'xlNotBetween' ist.
FormatConditions.Modify hat auch die gleiche Syntax wie FormatConditions.Add.
Es folgt eine Liste einiger Werte / Aufzählungen, die von einigen Parametern von 'Hinzufügen' / 'Ändern' verwendet werden können:
Beispiele für die bedingte VBA-Formatierung
Nachfolgend finden Sie Beispiele für die bedingte Formatierung in Excel VBA.
Beispiel 1
Nehmen wir an, wir haben eine Excel-Datei, die den Namen und die Noten einiger Schüler enthält, und wir möchten die Noten als fett und blau mit einer Farbe von mehr als 80 und als fett und rot mit einer Farbe von weniger als 80 bestimmen / hervorheben 50. Sehen wir uns die in der Datei enthaltenen Daten an:
Wir verwenden die FormatConditions. Fügen Sie die folgende Funktion hinzu, um dies zu erreichen:
- Gehen Sie zu Entwickler -> Visual Basic Editor:
- Klicken Sie mit der rechten Maustaste auf den Namen der Arbeitsmappe im Bereich 'Project-VBAProject' -> 'Insert' -> 'Module'.
- Schreiben Sie nun den Code / die Prozedur in dieses Modul:
Code:
Unterformatierung () End Sub
- Definieren Sie die Variablen rng, Bedingung1, Bedingung2:
Code:
Unterformatierung () Dim rng As Range Dim Bedingung1 As FormatCondition, Bedingung2 As FormatCondition End Sub
- Legen Sie mit der VBA-Funktion 'Bereich' den Bereich fest, für den eine bedingte Formatierung gewünscht wird:
Code:
Unterformatierung () Dim rng As Range Dim-Bedingung1 As FormatCondition, Bedingung2 As FormatCondition Set rng = Range ("B2", "B11") End Sub
- Löschen / Löschen vorhandener bedingter Formatierungen (falls vorhanden) aus dem Bereich mithilfe von 'FormatConditions.Delete':
Code:
Unterformatierung () Dim rng As Range Dim-Bedingung1 As FormatCondition, Bedingung2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub
- Definieren und legen Sie nun die Kriterien für jedes bedingte Format mithilfe von 'FormatConditions.Add' fest:
Code:
Unterformatierung () Dim rng As Range Dim Bedingung1 As FormatCondition, Bedingung2 As FormatCondition Set rng = Bereich ("B2", "B11") rng.FormatConditions.Delete Set Bedingung1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Setze Bedingung2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub
- Definieren und legen Sie das Format fest, das für jede Bedingung angewendet werden soll
Kopieren Sie diesen Code und fügen Sie ihn in Ihr VBA-Klassenmodul ein.
Code:
Sub formatting() 'Definining the variables: Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition 'Fixing/Setting the range on which conditional formatting is to be desired Set rng = Range("B2", "B11") 'To delete/clear any existing conditional formatting from the range rng.FormatConditions.Delete 'Defining and setting the criteria for each conditional format Set condition1 = rng.FormatConditions.Add(xlCellValue, xlGreater, "=80") Set condition2 = rng.FormatConditions.Add(xlCellValue, xlLess, "=50") 'Defining and setting the format to be applied for each condition With condition1 .Font.Color = vbBlue .Font.Bold = True End With With condition2 .Font.Color = vbRed .Font.Bold = True End With End Sub
Wenn wir diesen Code jetzt mit der Taste F5 oder manuell ausführen, sehen wir, dass Markierungen, die kleiner als 50 sind, fett und rot hervorgehoben werden, während Markierungen, die größer als 80 sind, wie folgt fett und blau hervorgehoben werden:
Beispiel 2
Nehmen wir im obigen Beispiel an, wir haben auch eine andere Spalte, in der angegeben ist, dass der Schüler ein "Topper" ist, wenn er mehr als 80 Punkte erzielt, andernfalls "Bestanden / Nicht Bestanden" gegen ihn geschrieben wurde. Jetzt möchten wir die als 'Topper' angegebenen Werte als Fett und Blau hervorheben. Lassen Sie uns die in der Datei enthaltenen Daten sehen:
In diesem Fall würde der Code / die Prozedur wie folgt funktionieren:
Code:
Sub TextFormatting () End Sub
Definieren und legen Sie das Format fest, das für jede Bedingung angewendet werden soll
Code:
Sub TextFormatting () With Range ("c2: c11"). FormatConditions.Add (xlTextString, TextOperator: = xlContains, String: = "topper") With .Font .Bold = True .Color = vbBlue End With End With End Sub
Wir können im obigen Code sehen, dass wir testen möchten, ob der Bereich: 'C2: C11' die Zeichenfolge: 'Topper' enthält, daher nimmt der Parameter: 'Onamestor' von 'Format.Add' die Aufzählung: 'Xcontains' an Testen Sie diese Bedingung im festen Bereich (dh C2: C11) und führen Sie dann die erforderliche bedingte Formatierung (Änderungen der Schriftart) für diesen Bereich durch.
Wenn wir diesen Code jetzt manuell oder durch Drücken der Taste F5 ausführen, sehen wir, dass Zellenwerte mit 'Topper' blau und fett hervorgehoben werden:
Below are some other instances/criteria that can be used to test and thus apply VBA conditional formatting on:
- Format by Time Period
- Average condition
- Colour Scale condition
- IconSet condition
- Databar condition
- Unique Values
- Duplicate Values
- Top10 values
- Percentile Condition
- Blanks Condition, etc.
With different conditions to be tested, different values/enumeration are taken by parameters of ‘Add.’
Things to Remember About VBA Conditional Formatting
- ‘Add’ method with ‘FormatConditions’ is used to create a new conditional format, ’Delete’ method to delete any conditional format, and ‘Modify’ method to alter any existing conditional format.
- The ‘Add’ method with ‘FormatConditions Collection’ fails if more than three conditional formats are created for a single range.
- Um mehr als drei bedingte Formate mit der Methode "Hinzufügen" auf einen Bereich anzuwenden, können Sie "Wenn" oder "Groß- / Kleinschreibung auswählen" verwenden.
- Wenn die 'Add'-Methode den' Type'-Parameter wie folgt hat: 'xlExpression', wird der Parameter 'Operator' ignoriert.
- Die Parameter 'Formel1' und 'Formel2' in der Methode 'Hinzufügen' können eine Zellreferenz, ein konstanter Wert, ein Zeichenfolgenwert oder sogar eine Formel sein.
- Der Parameter: 'Formel2' wird nur verwendet, wenn der Parameter: 'Operator' entweder 'xlBetween' oder 'xlNotBetween' ist, andernfalls wird er ignoriert.
- Um alle bedingten Formatierungen aus einem Arbeitsblatt zu entfernen, können Sie die Methode 'Löschen' wie folgt verwenden:
Cells.FormatConditions.Delete
