Podmienené formátovanie VBA Použite podmienený formát pomocou VBA Excel

Podmienené formátovanie v programe Excel VBA

Môžeme použiť podmienené formátovanie na bunku alebo rozsah buniek v programe Excel. Podmienený formát je formát, ktorý sa použije iba na bunky, ktoré spĺňajú určité kritériá, napríklad hodnoty nad konkrétnou hodnotou, kladné alebo záporné hodnoty alebo hodnoty s konkrétnym vzorcom atď. Toto podmienené formátovanie je možné vykonať aj v programovaní VBA programu Excel pomocou „ Zbierka formátovacích podmienok “ v makre / procedúre.

Podmienka formátu sa používa na vyjadrenie podmieneného formátu, ktorý je možné nastaviť volaním metódy, ktorá vráti premennú tohto typu. Obsahuje všetky podmienené formáty pre jeden rozsah a môže obsahovať iba tri podmienky formátu.

FormatConditions.Add / Modify / Delete sa vo VBA používa na pridanie / úpravu / odstránenie objektov FormatCondition do kolekcie. Každý formát je reprezentovaný objektom FormatCondition. FormatConditions je vlastnosťou objektu Range a pridajte nasledujúce parametre s nasledujúcou syntaxou:

FormatConditions.Add (Type, Operator, Formula1, Formula2)

Syntax Pridať vzorec obsahuje nasledujúce argumenty:

  • Typ: Povinné, predstavuje, ak je podmienený formát založený na hodnote prítomnej v bunke alebo výraze.
  • Operátor: Voliteľný, predstavuje operátor, ktorý sa má použiť s hodnotou, keď je typ založený na hodnote bunky.
  • Vzorec 1: Voliteľný, predstavuje hodnotu alebo výraz spojený s podmieneným formátom.
  • Formula2: Voliteľné, predstavuje hodnotu alebo výraz spojený s druhou časťou podmieneného formátu, keď je parameter: „Operator“ buď „xlB Between“ alebo „xlNotB Between“.

FormatConditions.Modify má tiež rovnakú syntax ako FormatConditions.Add.

Nasleduje zoznam niektorých hodnôt / výpočtov, ktoré je možné vykonať niektorými parametrami možnosti „Pridať“ / „Upraviť“:

Príklady podmieneného formátovania VBA

Nižšie uvádzame príklady podmieneného formátovania v programe Excel VBA.

Príklad č

Povedzme, že máme súbor Excel obsahujúci meno a známky niektorých študentov, a chceme určiť / zvýrazniť známky ako Tučné a modré farby, ktoré sú väčšie ako 80, a ako Tučné a Červené farby, ktoré sú menšie ako 50. Pozrime sa na údaje obsiahnuté v súbore:

Používame FormatConditions. Na dosiahnutie tohto cieľa pridajte nasledujúcu funkciu:

  • Prejsť na Vývojár -> Editor jazyka Visual Basic:
  • Kliknite pravým tlačidlom myši na názov zošita na table „Project-VBAProject“ -> „Vložiť“ -> „Modul“.
  • Teraz napíš kód / procedúru do tohto modulu:

Kód:

Subformátovanie () End Sub
  • Definujte premennú rng, podmienka1, podmienka2:

Kód:

Subformátovanie () Dim rng As Range Dim podmienka1 As FormatCondition, podmienka2 As FormatCondition End Sub
  • Pomocou funkcie VBA „Rozsah“ nastavte / opravte rozsah, v ktorom sa vyžaduje podmienené formátovanie:

Kód:

Subformátovanie () Dim rng As Range Dim podmienka1 As FormatCondition, podmienka2 As FormatCondition Set rng = Range ("B2", "B11") End Sub
  • Odstráňte / vymažte akékoľvek existujúce podmienené formátovanie (ak existuje) z rozsahu pomocou príkazu „FormatConditions.Delete“:

Kód:

Subformátovanie () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub
  • Teraz definujte a nastavte kritériá pre každý podmienený formát pomocou príkazu „FormatConditions.Add“:

Kód:

Subformátovanie () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Set condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Nastaviť podmienku2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub
  • Definujte a nastavte formát, ktorý sa má použiť pre každú podmienku

Skopírujte a vložte tento kód do svojho modulu triedy VBA.

Kód:

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

Keď teraz spustíme tento kód pomocou klávesu F5 alebo manuálne, vidíme, že značky, ktoré sú menšie ako 50, sú zvýraznené tučne a červenou farbou, zatiaľ čo tie, ktoré sú väčšie ako 80, sú zvýraznené tučne a modrou farbou takto:

Poznámka: Niektoré z vlastností vzhľadu formátovaných buniek, ktoré je možné použiť s FormatCondition, sú:

Príklad č

Povedzme, že vo vyššie uvedenom príklade máme ďalší stĺpec, v ktorom sa uvádza, že študent je „topper“, ak získa viac ako 80 známok, inak bude proti nim napísaný Pass / Fail. Teraz by sme chceli zvýrazniť hodnoty uvedené ako „Topper“ ako tučné a modré. Pozrime sa na údaje obsiahnuté v súbore:

V takom prípade bude kód / postup fungovať takto:

Kód:

Sub TextFormatting () End Sub

Definujte a nastavte formát, ktorý sa má použiť pre každú podmienku

Kód:

Sub TextFormatting () s rozsahom ("c2: c11"). FormatConditions.Add (xlTextString, TextOperator: = xlContains, String: = "topper") s .Font .Bold = True .Color = vbBlue Koniec Koniec Koniec Sub

Vo vyššie uvedenom kóde vidíme, že chceme otestovať, či rozsah: „C2: C11“ obsahuje reťazec: „Topper“, takže parameter: „Onamestor“ z „Format.Add“ vezme výčet: „Xcontains“ do túto podmienku otestujte v pevnom rozsahu (tj. C2: C11) a potom v tomto rozsahu vykonajte požadované podmienené formátovanie (zmeny písma).

Teraz, keď tento kód spustíme manuálne alebo stlačením klávesu F5, zistíme, že hodnoty buniek s výrazom „Topper“ sú zvýraznené modrou a hrubou farbou:

Poznámka: Vo vyššie uvedených dvoch príkladoch sme teda videli, ako metóda „Add“ funguje v prípade akýchkoľvek kritérií hodnoty bunky (číselný alebo textový reťazec).

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.
  • Ak chcete na rozsah použiť viac ako tri podmienené formáty pomocou metódy „Pridať“, môžeme použiť slová „Ak“ alebo „Vybrať malé a veľké písmená“.
  • Ak má metóda „Add“ svoj parameter „Type“ ako: „xlExpression“, potom je parameter „operátor“ ignorovaný.
  • Parametre: „Formula1“ a „Formula2“ v metóde „Add“ môžu byť odkaz na bunku, konštantná hodnota, hodnota reťazca alebo dokonca vzorec.
  • Parameter: 'Formula2' sa používa iba vtedy, keď je parameter: 'Operator' buď 'xlB Between' alebo 'xlNotB Between', inak je ignorovaný.
  • Na odstránenie všetkého podmieneného formátovania z ľubovoľného hárka môžeme použiť metódu „Odstrániť“ nasledovne:
Cells.FormatConditions.Delete

Zaujímavé články...