Použití podmíněného formátování v Excelu VBA

Podmíněné formátování v Excelu

Podmíněné formátování v Excelu vám umožňuje definovat pravidla, která určují formátování buněk.

Můžete například vytvořit pravidlo, které zvýrazní buňky, které splňují určitá kritéria. Mezi příklady patří:

• Čísla, která spadají do určitého rozsahu (např. Menší než 0).
• Prvních 10 položek v seznamu.
• Vytvoření „tepelné mapy“.
• Pravidla založená na vzorci pro prakticky jakékoli podmíněné formátování.

V aplikaci Excel najdete podmíněné formátování na pásu karet v části Domů> Styly (ALT> H> L).

Chcete -li vytvořit vlastní pravidlo, klikněte na „Nové pravidlo“ a zobrazí se nové okno:

Podmíněné formátování ve VBA

Ke všem těmto funkcím podmíněného formátování lze přistupovat pomocí VBA.

Všimněte si toho, že když nastavíte podmíněné formátování z kódu VBA, vaše nové parametry se objeví v okně podmíněného formátování frontendu Excelu a budou viditelné pro uživatele. Pokud je list nezamknete, bude je moci uživatel upravovat nebo mazat.

Při uložení listu se také uloží pravidla podmíněného formátování

Pravidla podmíněného formátování platí konkrétně pro konkrétní list a pro určitý rozsah buněk. Pokud jsou potřeba jinde v sešitu, musí být také nastaveny na tomto listu.

Praktické využití podmíněného formátování ve VBA

Můžete mít velký kus nezpracovaných dat importovaných do listu ze souboru CSV (hodnoty oddělené čárkami) nebo z databázové tabulky nebo dotazu. To může proudit do řídicího panelu nebo sestavy, přičemž se mění čísla importovaná z jednoho období do druhého.

Pokud se číslo změní a je mimo přijatelný rozsah, můžete na to upozornit např. barva pozadí buňky červeně a můžete to provést nastavením podmíněného formátování. Tímto způsobem bude uživatel okamžitě přitahován k tomuto číslu a poté může prozkoumat, proč se to děje.

Pomocí VBA můžete zapnout nebo vypnout podmíněné formátování. Pomocí VBA můžete vymazat pravidla pro řadu buněk nebo je znovu zapnout. Může nastat situace, kdy je k neobvyklému číslu naprosto dobrý důvod, ale když uživatel prezentuje řídicí panel nebo se hlásí vyšší úrovni managementu, chce mít možnost „zvonky alarmu“ odstranit.

Na nezpracovaných importovaných datech můžete také zdůraznit, kde jsou čísla směšně velká nebo směšně malá. Rozsah importovaných dat má obvykle pro každé období jinou velikost, takže můžete pomocí VBA vyhodnotit velikost nového rozsahu dat a vložit podmíněné formátování pouze pro tento rozsah.

Můžete také mít situaci, kdy existuje seřazený seznam jmen s číselnými hodnotami proti každému z nich, např. mzda zaměstnance, známky ze zkoušky. S podmíněným formátováním můžete použít odstupňované barvy k přechodu z nejvyšší na nejnižší, což pro účely prezentace vypadá velmi působivě.

Seznam jmen však nebude mít vždy statickou velikost a pomocí kódu VBA můžete aktualizovat měřítko odstupňovaných barev podle změn velikosti rozsahu.

Jednoduchý příklad vytvoření podmíněného formátu v rozsahu

Tento příklad nastavuje podmíněné formátování pro rozsah buněk (A1: A10) na listu. Pokud je číslo v rozsahu mezi 100 a 150, pak bude barva pozadí buňky červená, jinak nebude mít žádnou barvu.

1234567891011121314

Dílčí podmíněné formátování Příklad ()„Definujte rozsahDim MyRange As RangeNastavit MyRange = rozsah („A1: A10“)„Odstranit stávající podmíněné formátování z rozsahuMyRange.FormatConditions.Delete„Použít podmíněné formátováníMyRange.FormatConditions.Add Typ: = xlCellValue, operátor: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Všimněte si, že nejprve definujeme rozsah MyRange použít podmíněné formátování.

Dále odstraníme existující podmíněné formátování rozsahu. Je vhodné zabránit tomu, aby se stejné pravidlo přidávalo při každém spuštění kódu (samozřejmě to nebude za všech okolností vhodné).

Barvy jsou dány číselnými hodnotami. K tomu je dobré použít zápis RGB (červený, zelený, modrý). K tomu můžete použít standardní barevné konstanty, např. vbRed, vbBlue, ale máte omezený výběr osmi barev.

K dispozici je více než 16,7 milionu barev a pomocí RGB ke všem můžete přistupovat. Je to mnohem snazší, než se snažit zapamatovat si, které číslo odpovídá které barvě. Každé ze tří barevných čísel RGB je od 0 do 255.

Všimněte si, že parametr „xlBetween“ zahrnuje, takže podmínku splňují hodnoty buněk 100 nebo 150.

Vícepodmínečné formátování

Možná budete chtít ve svém datovém rozsahu nastavit několik podmíněných pravidel, aby se na všechny hodnoty v rozsahu vztahovaly různé podmínky:

12345678910111213141516171819

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range"Vytvořte objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Přidejte první pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, operátor: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)"Přidejte druhé pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, Operátor: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue"Přidejte třetí pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, operátor: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbYellowEnd Sub

Tento příklad nastavuje první pravidlo jako dříve, s barvou buňky červené, pokud je hodnota buňky mezi 100 a 150.

Poté se přidají další dvě pravidla. Pokud je hodnota buňky menší než 100, pak je barva buňky modrá a pokud je větší než 150, pak je barva buňky žlutá.

V tomto příkladu musíte zajistit, aby byly pokryty všechny možnosti čísel a aby se pravidla nepřekrývala.

Pokud jsou prázdné buňky v tomto rozsahu, pak se zobrazí jako modré, protože Excel je stále považuje za hodnotu menší než 100.

Cesta kolem je přidat další podmínku jako výraz. To je třeba přidat jako první pravidlo podmínky v kódu. Tam, kde existuje více pravidel, je velmi důležité správně určit pořadí provádění, jinak mohou být výsledky nepředvídatelné.

1234567891011121314151617181920212223

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range"Vytvořte objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Přidejte první pravidlo."MyRange.FormatConditions.Add Typ: = xlExpression, Formula1: = _"= DÉLKA (OŘEZ (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone"Přidejte druhé pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, operátor: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)"Přidejte třetí pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, Operátor: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue"Přidejte čtvrté pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, operátor: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)End Sub

To používá typ xlExpression a poté pomocí standardního vzorce aplikace Excel určí, zda je buňka místo číselné hodnoty prázdná.

Objekt FormatConditions je součástí objektu Range. Funguje stejně jako kolekce s indexem začínajícím na 1. Tento objekt můžete iterovat pomocí smyčky For… Next nebo For… Each.

Odstranění pravidla

Někdy může být nutné odstranit jednotlivá pravidla v sadě více pravidel, pokud nevyhovují požadavkům na data.

12345678910111213

Dílčí mazáníConditionalFormattingExample ()Dim MyRange As Range"Vytvořte objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Přidejte první pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, operátor: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)„Smazat pravidloMyRange.FormatConditions (1). OdstranitEnd Sub

Tento kód vytvoří nové pravidlo pro rozsah A1: A10 a poté jej odstraní. K odstranění musíte použít správné číslo rejstříku, proto zkontrolujte „Spravovat pravidla“ na frontendu Excelu (tím se zobrazí pravidla v pořadí provádění), abyste se ujistili, že získáte správné číslo rejstříku. Všimněte si toho, že v aplikaci Excel neexistuje možnost zrušení, pokud odstraníte pravidlo podmíněného formátování ve VBA, na rozdíl od toho, pokud to provedete prostřednictvím frontendu aplikace Excel.

Změna pravidla

Protože pravidla jsou souborem objektů založených na zadaném rozsahu, můžete v konkrétních pravidlech snadno provádět změny pomocí jazyka VBA. Skutečné vlastnosti po přidání pravidla jsou jen pro čtení, ale můžete je změnit pomocí metody Upravit. Vlastnosti, jako jsou barvy, se čtou / zapisují.

123456789101112131415

Dílčí změnaConditionalFormattingExample ()Dim MyRange As Range"Vytvořte objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Přidejte první pravidlo."MyRange.FormatConditions.Add Typ: = xlCellValue, operátor: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)„Změnit pravidloMyRange.FormatConditions (1). Upravit xlCellValue, xlLess, "10"„Změnit barvu pravidlaMyRange.FormatConditions (1) .Interior.Color = vbGreenEnd Sub

Tento kód vytvoří objekt rozsahu (A1: A10) a přidá pravidlo pro čísla mezi 100 a 150. Pokud je podmínka pravdivá, barva buňky se změní na červenou.

Kód poté změní pravidlo na čísla menší než 10. Pokud je podmínka pravdivá, barva buňky se nyní změní na zelenou.

Použití odstupňovaného barevného schématu

Podmíněné formátování v Excelu má prostředky k použití odstupňovaných barev na řadě čísel běžících vzestupně nebo sestupně.

To je velmi užitečné tam, kde máte data, jako jsou údaje o prodeji podle geografické oblasti, teploty města nebo vzdálenosti mezi městy. Pomocí VBA máte další výhodu v tom, že si můžete vybrat vlastní odstupňované barevné schéma namísto standardních nabízených na frontendu Excelu.

1234567891011121314151617181920212223242526272829

Podstupňované barvy ()Dim MyRange As Range"Vytvořit objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Definujte typ měřítka."MyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Vyberte barvu pro nejnižší hodnotu v rozsahuMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueS MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Barva = 7039480Konec s'Vyberte barvu pro střední hodnoty v rozsahuMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Hodnota = 50'Vyberte barvu pro střed rozsahuS MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Barva = 8711167Konec s'Vyberte barvu pro nejvyšší hodnotu v rozsahuMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueS MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Barva = 8109667Konec sEnd Sub

Když je tento kód spuštěn, bude gradovat barvy buněk podle vzestupných hodnot v rozsahu A1: A10.

Jedná se o velmi působivý způsob zobrazení dat a určitě upoutá pozornost uživatelů.

Podmíněné formátování pro chybové hodnoty

Když máte obrovské množství dat, může vám v různých listech snadno chybět chybová hodnota. Pokud je to uživateli předloženo, aniž by to bylo vyřešeno, mohlo by to vést k velkým problémům a uživatel by ztratil důvěru v čísla. K vyhodnocení buňky se používá typ pravidla xlExpression a excelová funkce IsError.

Můžete vytvořit kód tak, aby všechny buňky s chybami měly červenou barvu buňky:

1234567891011

Dílčí chybaConditionalFormattingExample ()Dim MyRange As Range"Vytvořit objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Přidat pravidlo chyby."MyRange.FormatConditions.Add Typ: = xlExpression, Formula1: = "= IsError (A1) = true"„Nastavte barvu interiéru na červenouMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Podmíněné formátování pro data v minulosti

Můžete mít importovaná data tam, kde chcete zvýraznit data, která jsou v minulosti. Příkladem toho může být zpráva dlužníka, kde chcete, aby vynikla jakákoli stará data faktury starší 30 dní.

Tento kód používá k vyhodnocení dat typ pravidla xlExpression a funkci aplikace Excel.

1234567891011

Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Vytvořte objekt rozsahu na základě sloupce datNastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete'Přidat chybové pravidlo pro data v minulosti.'MyRange.FormatConditions.Add Typ: = xlExpression, Formula1: = "= Nyní ()-A1> 30"„Nastavte barvu interiéru na červenouMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Tento kód bude mít rozsah dat v rozsahu A1: A10 a nastaví barvu buňky na červenou pro jakékoli datum, které je v minulosti starší než 30 dní.

Ve vzorci použitém v podmínce uvádí Now () aktuální datum a čas. To se bude přepočítávat pokaždé, když se přepočítá list, takže se formátování změní ze dne na den.

Použití datových pruhů v podmíněném formátování VBA

Pomocí VBA můžete přidat datové pruhy do řady čísel. Jsou to téměř jako mini grafy a poskytují okamžitý přehled o tom, jak velká jsou čísla ve vzájemném vztahu. Přijetím výchozích hodnot pro datové pruhy se kód velmi snadno zapisuje.

123456

Sub DataBarFormattingExample ()Dim MyRange As RangeNastavit MyRange = rozsah („A1: A10“)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub

Vaše data budou na listu vypadat takto:

Použití ikon v podmíněném formátování VBA

Podmíněné formátování můžete použít k vložení ikon vedle čísel na list. Ikonami mohou být šipky nebo kruhy nebo různé jiné tvary. V tomto příkladu kód přidá k číslům ikony šipek na základě jejich procentních hodnot:

12345678910111213141516171819202122232425

Dílčí sady ikon Příklad ()Dim MyRange As Range"Vytvořit objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Přidat sadu ikon do objektu FormatConditions."MyRange.FormatConditions.AddIconSetCondition„Nastavte sadu ikon na šipky - podmínka 1With MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Konec s'nastavte kritéria ikon pro požadovanou procentní hodnotu - podmínka 2S MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Hodnota = 33.Operator = xlGreaterEqualKonec s'nastavte kritéria ikon pro požadovanou procentní hodnotu - podmínka 3S MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Hodnota = 67.Operator = xlGreaterEqualKonec sEnd Sub

To poskytne okamžitý přehled o tom, zda je číslo vysoké nebo nízké. Po spuštění tohoto kódu bude váš list vypadat takto:

Použití podmíněného formátování pro zvýraznění první pětky

Pomocí kódu VBA můžete zvýraznit 5 nejlepších čísel v datovém rozsahu. Používáte parametr nazvaný „AddTop10“, ale můžete upravit číslo pozice v kódu na 5. Uživatel si může přát vidět nejvyšší čísla v rozsahu, aniž by musel data nejprve třídit.

1234567891011121314151617181920212223

Sub Top5Příklad ()Dim MyRange As Range"Vytvořit objekt rozsahu."Nastavit MyRange = rozsah („A1: A10“)'Odstranit předchozí podmíněné formátyMyRange.FormatConditions.Delete"Přidejte podmínku Top10."MyRange.FormatConditions.AddTop10With MyRange.FormatConditions (1)'Nastavit parametr shora dolů.'.TopBottom = xlTop10Top„Nastavit pouze top 5. Pořadí = 5Konec sS MyRange.FormatConditions (1) .Font„Nastavte barvu písma.Barva = -16383844Konec sWith MyRange.FormatConditions (1) .Interior`` Nastavte barvu pozadí buňky.Barva = 13551615Konec sEnd Sub

Data na vašem listu by po spuštění kódu vypadala takto:

Všimněte si, že hodnota 145 se zobrazí dvakrát, takže je zvýrazněno šest buněk.

Význam parametrů StopIfTrue a SetFirstPriority

StopIfTrue je důležité, pokud má řada buněk několik pravidel podmíněného formátování. Jedna buňka v rozsahu může splňovat první pravidlo, ale může také splňovat následující pravidla. Jako vývojář můžete chtít, aby zobrazoval formátování pouze pro první pravidlo, na které přijde. Ostatní kritéria pravidel se mohou překrývat a mohou provádět nechtěné změny, pokud jim bude povoleno pokračovat v seznamu pravidel.

Výchozí hodnota tohoto parametru je True, ale můžete ji změnit, pokud chcete, aby byla zohledněna všechna ostatní pravidla pro tuto buňku:

1	MyRange. FormatConditions (1) .StopIfTrue = False

Parametr SetFirstPriority určuje, zda bude toto pravidlo podmínky vyhodnoceno jako první, pokud pro danou buňku existuje více pravidel.

1	MyRange. FormatConditions (1). SetFirstPriority

Tím se přesune pozice tohoto pravidla na pozici 1 v rámci kolekce formátových podmínek a všechna další pravidla budou přesunuta dolů se změněnými čísly indexů. Dávejte pozor, pokud provádíte jakékoli změny pravidel v kódu pomocí čísel indexů. Musíte se ujistit, že měníte nebo odstraňujete správné pravidlo.

Prioritu pravidla můžete změnit:

1	MyRange. FormatConditions (1). Priorita = 3

Tím se změní relativní pozice všech ostatních pravidel v seznamu podmíněného formátu.

Použití podmíněného formátování odkazující na jiné hodnoty buněk

To je jedna věc, kterou podmíněné formátování v Excelu neumí. K tomu si však můžete vytvořit vlastní kód VBA.

Předpokládejme, že máte sloupec dat a v sousední buňce u každého čísla je nějaký text, který udává, jaké formátování by mělo na každém čísle probíhat.

Následující kód spustí váš seznam čísel, vyhledá formátování textu v sousední buňce a poté číslo naformátuje podle potřeby:

123456789101112131415161718192021

Sub ReferToAnotherCellForConditionalFormatting ()'Vytvořte proměnné, které budou uchovávat počet řádků pro tabulková data.'Dim RRow As Long, N As Long'Zachyťte počet řádků v rozsahu tabulkových dat.'RRow = ActiveSheet.UsedRange.Rows.Count"Opakujte všechny řádky v rozsahu tabulkových dat."Pro N = 1 do RRow'Pomocí příkazu Select Case vyhodnotíte formátování na základě sloupce 2Vyberte Case ActiveSheet.Cells (N, 2). Value„Změňte barvu interiéru na modrouPouzdro "modré"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue„Změňte barvu interiéru na červenouPouzdro "červené"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed„Změňte barvu interiéru na zelenouPouzdro "zelené"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenKonec VybratDalší N.End Sub

Po spuštění tohoto kódu bude váš list nyní vypadat takto:

Buňky, na které se odkazuje pro formátování, mohou být kdekoli na listu nebo dokonce na jiném listu v sešitu. K vytvoření podmínky pro formátování můžete použít libovolnou formu textu a jste omezeni pouze svou představivostí v způsobech použití, do kterých byste tento kód mohli vložit.

Operátory, které lze použít v prohlášení o podmíněném formátování

Jak jste viděli v předchozích příkladech, operátory se používají k určení, jak budou vyhodnoceny hodnoty podmínek, např. xlMezi nimi.

Existuje několik těchto operátorů, které lze použít, v závislosti na tom, jak chcete zadat kritéria pravidel.

název	Hodnota	Popis
xlMezi nimi	1	Mezi. Lze použít pouze v případě, že jsou k dispozici dva vzorce.
xlRovný	3	Rovnat se.
xl Větší	5	Větší než.
xlGreaterEqual	7	Větší než nebo rovno.
xlMéně	6	Méně než.
xlLessEqual	8	Méně než nebo rovno.
xlNeBetween	2	Ne mezi. Lze použít pouze v případě, že jsou k dispozici dva vzorce.
xlNepEqual	4	Není rovno.