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 použije 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 určitý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 přidejte 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.
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 údaje obsažené v souboru:
K dosažení tohoto cíle používáme FormatConditions.Add the function as below:
- 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ínka 1 As FormatCondition, podmínka 2 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:
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 napsá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: „Onamestor“ v „Format.Add“ přebírá výčet: „Xcontains“ na otestujte tuto podmínku v pevném rozsahu (tj. C2: C11) a poté v tomto rozsahu proveďte 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ě:
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.
- 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 buď '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
