VBA-bedingte Formatierung

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 und Add verfügt über folgende Parameter mit der folgenden Syntax:

FormatConditions.Add (Typ, Operator, Formel1, Formel2)

Die Syntax zum Hinzufügen von Formeln enthält folgende 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.

Sie können diese Vorlage für die bedingte VBA-Formatierung hier herunterladen - Vorlage für die bedingte VBA-Formatierung

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 50 bestimmen / hervorheben. Lassen Sie uns die in der Datei enthaltenen Daten sehen:

Wir verwenden die FormatConditions.Add-Funktion wie folgt, um dies zu erreichen:

  • Gehen Sie zu Entwickler -> Visual Basic-Editor:

  • Klicken Sie mit der rechten Maustaste auf den Arbeitsmappennamen 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:

Unterformatierung () 'Definieren der Variablen: Dim rng As Range Dim Bedingung1 As FormatCondition, Bedingung2 As FormatCondition' Festlegen / Einstellen des Bereichs, in dem die bedingte Formatierung gewünscht werden soll Set rng = Range ("B2", "B11") 'To Löschen / Löschen vorhandener bedingter Formatierungen aus dem Bereich rng.FormatConditions.Delete 'Definieren und Festlegen der Kriterien für jedes bedingte Format Setzen Sie Bedingung1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Setzen Sie Bedingung2 = rng.FormatConditions. Add (xlCellValue, xlLess, "= 50") 'Definieren und Festlegen des Formats, das für jede Bedingung angewendet werden soll. Mit Bedingung1 .Font.Color = vbBlue .Font.Bold = True End With With Bedingung2 .Font.Color = vbRed .Font. Fett = 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:

Hinweis: Einige der Eigenschaften für das Erscheinungsbild formatierter Zellen, die mit FormatCondition verwendet werden können, sind:

Beispiel 2

Nehmen wir im obigen Beispiel an, wir haben auch eine andere Spalte, die besagt, dass der Schüler ein "Topper" ist, wenn er mehr als 80 Punkte erzielt, andernfalls wird "Bestanden / Nicht Bestanden" gegen ihn geschrieben. 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

We can see in the above code that we wish to test if the range: ‘C2:C11” contains the string: “Topper”, so the parameter: “Operator” of ‘Format.Add’ takes the enumeration:”xlContains”, to test this condition in the fixed range (i.e C2:C11), and then do the required conditional formatting (font changes) on this range.

Now when we run this code manually or by pressing the F5 key, we see that cell values with ‘Topper’ get highlighted in Blue and bold:

Note: So, we have seen in the above two examples how the ‘Add’ method works in case of any cell value criteria (numeric or text string).

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.
  • To apply more than three conditional formats to a range using the ‘Add’ method, we can use ‘If’ or ‘select case’.
  • If the ‘Add’ method has its ‘Type’ parameter as: ‘xlExpression’, then the parameter ‘Operator’ is ignored.
  • The parameters: ‘Formula1’ and ‘Formula2’ in the ‘Add’ method can be a cell reference, constant value, string value, or even a formula.
  • The parameter: ‘Formula2’ is used only when the parameter: ‘Operator’ is either ‘xlBetween’ or ‘xlNotBetween’, else it is ignored.
  • To remove all the conditional formatting from any worksheet, we can use the ‘Delete’ method as follows:
Cells.FormatConditions.Delete