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 Voeg de volgende parameters toe 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 is gekoppeld aan de voorwaardelijke opmaak.
- 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 kunnen worden gebruikt door enkele parameters van 'Toevoegen' / 'Wijzigen':
Voorbeelden van voorwaardelijke opmaak van VBA
Hieronder staan de voorbeelden van voorwaardelijke opmaak in Excel VBA.
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 van kleur, wat groter is dan 80, en als Vet en Rood van kleur, wat minder is dan 50. Laten we eens kijken naar de gegevens in het bestand:
We gebruiken de FormatConditions. Voeg de onderstaande functie toe om dit te bereiken:
- Ga naar Developer -> Visual Basic Editor:
- Klik met de rechtermuisknop op de werkmapnaam in het deelvenster 'Project-VBAProject' -> '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:
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
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:
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 () Met bereik ("c2: c11"). FormatConditions.Add (xlTextString, TextOperator: = xlContains, String: = "topper") Met .Font .Bold = True .Color = vb Blauw Einde met Einde met einde Sub
We kunnen in de bovenstaande code zien dat we willen testen of het bereik: 'C2: C11' de string bevat: 'Topper', dus de parameter: 'Onamestor' van 'Format.Add' neemt de opsomming: 'Xcontains' naar test deze voorwaarde in het vaste bereik (dwz C2: C11), en voer vervolgens de vereiste voorwaardelijke opmaak (lettertype-wijzigingen) uit in dit bereik.
Wanneer we deze code nu handmatig uitvoeren of door op de F5-toets te drukken, zien we dat celwaarden met 'Topper' blauw en vet worden gemarkeerd:
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.
- Om meer dan drie voorwaardelijke opmaak toe te passen op een bereik met behulp van de 'Toevoegen'-methode, kunnen we' Als 'of' selecteer case 'gebruiken.
- Als de 'Add'-methode zijn' Type'-parameter heeft als: 'xlExpression', dan wordt de parameter 'Operator' genegeerd.
- De parameters: 'Formule1' en 'Formule2' in de 'Toevoegen'-methode kunnen een celverwijzing, constante waarde, tekenreekswaarde of zelfs een formule zijn.
- De parameter: 'Formula2' wordt alleen gebruikt als de parameter: 'Operator' ofwel 'xlBetween' of 'xlNotBetween' is, anders wordt deze genegeerd.
- Om alle voorwaardelijke opmaak van een werkblad te verwijderen, kunnen we de 'Verwijderen'-methode als volgt gebruiken:
Cells.FormatConditions.Delete
