VBA voorwaardelijke opmaak

Voorwaardelijke opmaak in Excel VBA

We kunnen voorwaardelijke opmaak toepassen op een cel of celbereik in Excel. Een voorwaardelijke opmaak is een indeling die alleen wordt toegepast op cellen die aan bepaalde criteria voldoen, bijvoorbeeld waarden boven een bepaalde waarde, positieve of negatieve waarden, of waarden met een bepaalde formule, enz. Deze voorwaardelijke opmaak kan ook worden gedaan in Excel VBA-programmering met de ' Formaatcondities Verzameling ' in de macro / procedure.

Opmaakvoorwaarde wordt gebruikt om een ​​voorwaardelijke opmaak weer te geven die kan worden ingesteld door een methode aan te roepen die een variabele van dat type retourneert. Het bevat alle voorwaardelijke opmaak voor een enkel bereik en kan slechts drie opmaakvoorwaarden bevatten.

FormatConditions.Add / Modify / Delete wordt in VBA gebruikt om FormatCondition-objecten aan de collectie toe te voegen / te wijzigen / te verwijderen. Elk formaat wordt vertegenwoordigd door een FormatCondition-object. FormatConditions is een eigenschap van het Range-object en Add heeft de volgende parameters met onderstaande syntaxis:

FormatConditions.Add (Type, Operator, Formule1, Formule2)

De syntaxis van formule toevoegen heeft de volgende argumenten:

  • Type: vereist, geeft aan of de voorwaardelijke opmaak is gebaseerd op de waarde die aanwezig is in de cel of een uitdrukking
  • Operator: optioneel, vertegenwoordigt de operator die moet worden gebruikt met een waarde wanneer 'Type' is gebaseerd op de celwaarde
  • Formule1: optioneel, vertegenwoordigt de waarde of uitdrukking die aan de voorwaardelijke opmaak is gekoppeld.
  • Formule2: Optioneel, vertegenwoordigt de waarde of uitdrukking die is gekoppeld aan het tweede deel van voorwaardelijke opmaak wanneer de parameter: 'Operator' ofwel 'xlBetween' of 'xlNotBetween' is

FormatConditions.Modify heeft ook dezelfde syntaxis als FormatConditions.Add.

Hieronder volgt de lijst met enkele waarden / opsomming die kan worden gebruikt door enkele parameters van 'Toevoegen' / 'Wijzigen':

Voorbeelden van voorwaardelijke opmaak van VBA

Hieronder staan ​​de voorbeelden van voorwaardelijke opmaak in Excel vba.

U kunt deze VBA-sjabloon voor voorwaardelijke opmaak hier downloaden - VBA-sjabloon voor voorwaardelijke opmaak

Voorbeeld 1

Laten we zeggen dat we een Excel-bestand hebben met de naam en cijfers van enkele studenten, en we willen de markeringen bepalen / markeren als vet en blauw in kleur die groter is dan 80, en als vet en rood in kleur die minder is dan 50. Laten we de gegevens in het bestand eens bekijken:

We gebruiken de FormatConditions.Add-functie zoals hieronder om dit te bereiken:

  • Ga naar Developer -> Visual Basic Editor:

  • Klik met de rechtermuisknop op de werkmapnaam in het 'Project-VBAProject' paneel-> 'Invoegen' -> 'Module'.

  • Schrijf nu de code / procedure in deze module:

Code:

 Subopmaak () End Sub

  • Definieer de variabele rng, condition1, condition2:

Code:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition End Sub

  • Stel het bereik in waarop voorwaardelijke opmaak gewenst is met behulp van de VBA-functie 'Bereik':

Code:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") End Sub

  • Verwijder / wis bestaande voorwaardelijke opmaak (indien aanwezig) uit het bereik met behulp van 'FormatConditions.Delete':

Code:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub

  • Definieer nu en stel de criteria voor elk voorwaardelijk formaat in met behulp van 'FormatConditions.Add':

Code:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Stel condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Stel condition2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub

  • Definieer en stel het formaat in dat voor elke voorwaarde moet worden toegepast

Kopieer en plak deze code in uw VBA-klassenmodule.

Code:

Subformatting () 'De variabelen definiëren: Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition' Vaststellen / instellen van het bereik waarop voorwaardelijke opmaak gewenst is Set rng = Range ("B2", "B11") 'To verwijder / wis bestaande voorwaardelijke opmaak uit het bereik rng.FormatConditions.Delete 'Definiëren en instellen van de criteria voor elke voorwaardelijke indeling Stel condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Stel condition2 = rng.FormatConditions in. Add (xlCellValue, xlLess, "= 50") 'Definiëren en instellen van het formaat dat moet worden toegepast voor elke conditie Met condition1 .Font.Color = vbBlue .Font.Bold = Echt einde met Met conditie2 .Font.Color = vbRed .Font. Vet = True End met End Sub

Wanneer we deze code nu uitvoeren met de F5-toets of handmatig, zien we dat de markeringen die kleiner zijn dan 50 vetgedrukt en rood worden gemarkeerd, terwijl die groter dan 80 als volgt vetgedrukt en blauw worden gemarkeerd:

Opmerking: enkele van de eigenschappen voor het uiterlijk van opgemaakte cellen die kunnen worden gebruikt met FormatCondition zijn:

Voorbeeld # 2

Laten we zeggen dat we in het bovenstaande voorbeeld ook een andere kolom hebben waarin staat dat de student een 'Topper' is als hij / zij meer dan 80 punten scoort, anders wordt Pass / Fail tegen hen geschreven. Nu willen we de waarden die worden vermeld als 'Topper' markeren als Vet en Blauw. Laten we de gegevens in het bestand eens bekijken:

In dit geval zou de code / procedure als volgt werken:

Code:

 Sub TextFormatting () End Sub

Definieer en stel het formaat in dat voor elke voorwaarde moet worden toegepast

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