Podmíněné formátování VBA | Použijte podmíněný formát pomocí VBA Excel

Podmíněné formátování v aplikaci Excel VBA

Můžeme použít podmíněné formátování na buňku nebo rozsah buněk v aplikaci Excel. Podmíněný formát je formát, který se aplikuje pouze na buňky, které splňují určitá kritéria, řekněme hodnoty nad určitou hodnotou, kladné nebo záporné hodnoty nebo hodnoty s konkrétním vzorcem atd. Toto podmíněné formátování lze provést také v programování VBA aplikace Excel pomocí „ Formátování podmínek shromažďování “ v makru / proceduře.

Podmínka formátu se používá k reprezentaci podmíněného formátu, který lze nastavit voláním metody, která vrací proměnnou daného typu. Obsahuje všechny podmíněné formáty pro jeden rozsah a může obsahovat pouze tři podmínky formátu.

FormatConditions.Add / Modify / Delete se ve VBA používá k přidávání / úpravám / mazání objektů FormatCondition do kolekce. Každý formát je reprezentován FormatCondition objektem. FormatConditions je vlastnost Range objektu a Add má následující parametry s níže uvedenou syntaxí:

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

Syntaxe Přidat vzorec obsahuje následující argumenty:

  • Typ: Povinný, představuje, pokud je podmíněný formát založen na hodnotě přítomné v buňce nebo ve výrazu
  • Operátor: Volitelné, představuje operátor, který má být použit s hodnotou, když je typ založen na hodnotě buňky
  • Formula1: Volitelné, představuje hodnotu nebo výraz spojený s podmíněným formátem.
  • Formula2: Volitelné, představuje hodnotu nebo výraz spojený s druhou částí podmíněného formátu, když je parametr: „Operátor“ buď „xlB Between“ nebo „xlNotB Between“.

FormatConditions.Modify má také stejnou syntaxi jako FormatConditions.Add.

Následuje seznam některých hodnot / výčtu, které lze převzít některými parametry funkce „Přidat“ / „Upravit“:

Příklady podmíněného formátování VBA

Níže jsou uvedeny příklady podmíněného formátování v aplikaci Excel VBA.

Tuto šablonu podmíněného formátování VBA si můžete stáhnout zde - Šablona podmíněného formátování VBA

Příklad č. 1

Řekněme, že máme soubor Excel obsahující jméno a známky některých studentů a chceme určit / zvýraznit známky jako tučné a modré barvy, které jsou větší než 80, a jako tučné a červené barvy, které jsou menší než 50. Podívejme se na data obsažená v souboru:

K dosažení tohoto cíle používáme níže uvedenou funkci FormatConditions.Add:

  • Přejít na Vývojář -> Editor jazyka:

  • Klikněte pravým tlačítkem na název sešitu v podokně „Project-VBAProject“ -> „Vložit“ -> „Modul“.

  • Nyní napište kód / proceduru do tohoto modulu:

Kód:

 Subformátování () End Sub 

  • Definujte proměnnou rng, podmínka1, podmínka2:

Kód:

 Subformátování () Dim rng As Range Dim podmínka1 As FormatCondition, podmínka2 As FormatCondition End Sub 

  • Pomocí funkce VBA 'Range' nastavte / opravte rozsah, ve kterém má být podmíněné formátování požadováno:

Kód:

 Subformátování () Dim rng As Range Dim podmínka 1 As FormatCondition, podmínka 2 As FormatCondition Set rng = Range ("B2", "B11") End Sub 

  • Odstranit / vymazat jakékoli existující podmíněné formátování (pokud existuje) z rozsahu pomocí 'FormatConditions.Delete':

Kód:

 Subformátování () Dim rng As Range Dim podmínka 1 As FormatCondition, podmínka 2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub

  • Nyní definujte a nastavte kritéria pro každý podmíněný formát pomocí 'FormatConditions.Add':

Kód:

 Subformátování () 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 ") Nastavit podmínku2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub 

  • Definujte a nastavte formát, který se použije pro každou podmínku

Zkopírujte a vložte tento kód do svého modulu třídy VBA.

Kód:

Sub formatting () 'Definování proměnných: Dim rng As Range Dim podmínka 1 As FormatCondition, podmínka 2 As FormatCondition' Oprava / nastavení rozsahu, ve kterém má být podmíněné formátování požadováno Set rng = Range ("B2", "B11") 'To smazat / vymazat jakékoli stávající podmíněné formátování z rozsahu rng.FormatConditions.Delete 'Definování a nastavení kritérií pro každý podmíněný formát Nastavit podmínku1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Nastavit podmínku2 = rng.FormatConditions. Přidat (xlCellValue, xlLess, "= 50") 'Definování a nastavení formátu, který se použije pro každou podmínku S podmínkou1 .Font.Color = vbBlue .Font.Bold = Pravý konec s podmínkou2 .Font.Color = vbRed .Font. Bold = True End With End Sub

Nyní, když spustíme tento kód pomocí klávesy F5 nebo ručně, vidíme, že značky, které jsou menší než 50, jsou zvýrazněny tučně a červeně, zatímco ty, které jsou větší než 80, jsou zvýrazněny tučně a modře takto:

Poznámka: Některé z vlastností vzhledu formátovaných buněk, které lze použít s FormatCondition, jsou:

Příklad č. 2

Řekněme, že ve výše uvedeném příkladu máme další sloupec, který uvádí, že student je „Topper“, pokud získá více než 80 známek, jinak je proti nim zapsáno Pass / Fail. Nyní bychom chtěli zvýraznit hodnoty uvedené jako „Topper“ jako tučné a modré. Podívejme se na data obsažená v souboru:

V tomto případě by kód / postup fungoval následovně:

Kód:

 Sub TextFormatting () End Sub 

Definujte a nastavte formát, který se použije pro každou podmínku

Kód:

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

Ve výše uvedeném kódu vidíme, že chceme otestovat, zda rozsah: „C2: C11“ obsahuje řetězec: „Topper“, takže parametr: „Operator“ v „Format.Add“ přebírá výčet: „xlContains“, otestovat tuto podmínku v pevném rozsahu (tj. C2: C11) a poté v tomto rozsahu provést požadované podmíněné formátování (změny písma).

Nyní, když tento kód spustíme ručně nebo stisknutím klávesy F5, vidíme, že hodnoty buněk s 'Topperem' jsou zvýrazněny modře a tučně:

Poznámka: Ve výše uvedených dvou příkladech jsme tedy viděli, jak metoda „Přidat“ funguje v případě jakýchkoli kritérií hodnot buňky (číselný nebo textový řetězec).

Níže uvádíme několik dalších instancí / kritérií, která lze použít k testování a tedy použití podmíněného formátování VBA na:

  • Formátovat podle časového období
  • Průměrný stav
  • Stav barevné škály
  • IconSet stav
  • Stav databáze
  • Unikátní hodnoty
  • Duplicitní hodnoty
  • Top 10 hodnot
  • Percentilní stav
  • Prázdné podmínky atd.

S různými podmínkami, které mají být testovány, jsou parametry 'Add' převzaty různé hodnoty / výčet.

Důležité informace o podmíněném formátování VBA

  • Metoda „Přidat“ s metodou „FormatConditions“ se používá k vytvoření nového podmíněného formátu, metoda „Odstranit“ k odstranění jakéhokoli podmíněného formátu a metoda „Upravit“ ke změně existujícího podmíněného formátu.
  • Metoda „Přidat“ s kolekcí „FormatConditions Collection“ selže, pokud jsou pro jeden rozsah vytvořeny více než tři podmíněné formáty.
  • Chcete-li použít více než tři podmíněné formáty na rozsah pomocí metody „Přidat“, můžeme použít „If“ nebo „select case“.
  • Pokud má metoda „Přidat“ parametr „Typ“ jako: „xlExpression“, pak je parametr „Operátor“ ignorován.
  • Parametry: „Formula1“ a „Formula2“ v metodě „Add“ mohou být odkaz na buňku, konstantní hodnota, hodnota řetězce nebo dokonce vzorec.
  • Parametr: 'Formula2' se používá pouze v případě, že parametr: 'Operator' je 'xlB Between' nebo 'xlNotB Between', jinak je ignorován.
  • Chcete-li odebrat veškeré podmíněné formátování z libovolného listu, můžeme použít metodu 'Odstranit' následujícím způsobem:
Cells.FormatConditions.Delete