Utilizarea formatării condiționate cu Excel VBA

Formatare condiționată Excel

Formatarea condiționată Excel vă permite să definiți reguli care determină formatarea celulei.

De exemplu, puteți crea o regulă care să evidențieze celulele care îndeplinesc anumite criterii. Exemplele includ:

• Numere care se încadrează într-un anumit interval (de exemplu, mai puțin de 0).
• Primele 10 articole dintr-o listă.
• Crearea unei „hărți de căldură”.
• Reguli „bazate pe formulă” pentru practic orice formatare condiționată.

În Excel, formatarea condiționată poate fi găsită în panglică sub Acasă> Stiluri (ALT> H> L).

Pentru a crea propria regulă, faceți clic pe „Regula nouă” și va apărea o nouă fereastră:

Formatare condiționată în VBA

Toate aceste funcții de formatare condiționată pot fi accesate folosind VBA.

Rețineți că atunci când configurați formatarea condiționată din codul VBA, noii parametri vor apărea în fereastra de formatare condiționată front-end Excel și vor fi vizibili pentru utilizator. Utilizatorul va putea să le editeze sau să le șteargă dacă nu ați blocat foaia de lucru.

Regulile de formatare condiționată sunt, de asemenea, salvate atunci când foaia de lucru este salvată

Regulile condiționate de formatare se aplică în mod specific unei anumite foi de lucru și unui anumit interval de celule. Dacă sunt necesare în altă parte a registrului de lucru, atunci trebuie să fie configurate și pe foaia de lucru respectivă.

Utilizări practice ale formatării condiționate în VBA

Este posibil să aveți o cantitate mare de date brute importate în foaia de lucru dintr-un fișier CSV (valori separate prin virgulă) sau dintr-un tabel sau o interogare a bazei de date. Acest lucru poate trece într-un tablou de bord sau un raport, cu modificarea numerelor importate de la o perioadă la alta.

În cazul în care un număr se schimbă și se află în afara unui interval acceptabil, vă recomandăm să evidențiați acest lucru, de ex. culoarea de fundal a celulei în roșu și puteți face acest lucru configurând formatarea condiționată. În acest fel, utilizatorul va fi atras instantaneu de acest număr și va putea apoi investiga de ce se întâmplă acest lucru.

Puteți utiliza VBA pentru a activa sau a dezactiva formatarea condiționată. Puteți utiliza VBA pentru a șterge regulile pentru o gamă de celule sau pentru a le reactiva din nou. Poate exista o situație în care există un motiv perfect bun pentru un număr neobișnuit, dar atunci când utilizatorul prezintă tabloul de bord sau raportează la un nivel mai ridicat de management, vrea să poată elimina „clopotele de alarmă”.

De asemenea, pe datele brute importate, poate doriți să evidențiați unde numerele sunt ridicol de mari sau ridicol de mici. Intervalul de date importat are, de obicei, o dimensiune diferită pentru fiecare perioadă, deci puteți utiliza VBA pentru a evalua dimensiunea noului interval de date și pentru a insera formatarea condiționată numai pentru intervalul respectiv.

Este posibil să aveți și o situație în care există o listă sortată de nume cu valori numerice față de fiecare, de exemplu salariul angajaților, note de examen. Cu formatarea condiționată, puteți utiliza culori gradate pentru a merge de la cel mai mare la cel mai mic, ceea ce arată foarte impresionant în scopuri de prezentare.

Cu toate acestea, lista numelor nu va fi întotdeauna statică ca dimensiune și puteți utiliza codul VBA pentru a reîmprospăta scala culorilor gradate în funcție de modificările dimensiunii gamei.

Un exemplu simplu de creare a unui format condiționat pe un interval

Acest exemplu setează formatarea condiționată pentru un interval de celule (A1: A10) pe o foaie de lucru. Dacă numărul din interval este între 100 și 150, atunci culoarea de fundal a celulei va fi roșie, altfel nu va avea culoare.

1234567891011121314

Sub ConditionalFormattingExample ()‘Definiți gamaDim MyRange As RangeSetați MyRange = Range („A1: A10”)‘Ștergeți formatarea condițională existentă din gamăMyRange.FormatConditions.Delete‘Aplicați formatarea condiționatăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Sfârșitul Sub

Observați că mai întâi definim intervalul MyRange pentru a aplica formatarea condiționată.

Apoi ștergem orice formatare condițională existentă pentru interval. Aceasta este o idee bună pentru a preveni adăugarea aceleiași reguli de fiecare dată când se rulează codul (desigur că nu va fi adecvat în toate circumstanțele).

Culorile sunt date de valori numerice. Este o idee bună să utilizați notația RGB (roșu, verde, albastru) pentru aceasta. Puteți utiliza constante de culoare standard pentru aceasta, de ex. vbRed, vbBlue, dar sunteți limitat la opt opțiuni de culoare.

Există peste 16,7 milioane de culori disponibile, iar folosind RGB le puteți accesa pe toate. Acest lucru este mult mai ușor decât să încercați să vă amintiți ce număr se potrivește cu ce culoare. Fiecare dintre cele trei numere de culoare RGB este de la 0 la 255.

Rețineți că parametrul „xlBetween” este inclus, astfel încât valorile celulei de 100 sau 150 vor satisface condiția.

Formatare multi-condițională

Poate doriți să configurați mai multe reguli condiționale în intervalul dvs. de date, astfel încât toate valorile dintr-un interval să fie acoperite de condiții diferite:

12345678910111213141516171819

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete'Adăugați prima regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Adăugați a doua regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Adăugați a treia regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbGălbenSfârșitul Sub

Acest exemplu setează prima regulă ca înainte, cu culoarea roșie a celulei dacă valoarea celulei este între 100 și 150.

Se adaugă apoi încă două reguli. Dacă valoarea celulei este mai mică de 100, atunci culoarea celulei este albastră și, dacă este mai mare de 150, atunci culoarea celulei este galbenă.

În acest exemplu, trebuie să vă asigurați că toate posibilitățile numerelor sunt acoperite și că regulile nu se suprapun.

Dacă celulele goale sunt în acest interval, atunci acestea vor fi afișate ca albastre, deoarece Excel le consideră în continuare ca având o valoare mai mică de 100.

Modalitatea în jurul acestui lucru este de a adăuga într-o altă condiție ca expresie. Aceasta trebuie adăugată ca primă regulă de condiție din cod. Este foarte important acolo unde există mai multe reguli, pentru a obține ordinea de execuție corectă, altfel rezultatele pot fi imprevizibile.

1234567891011121314151617181920212223

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete'Adăugați prima regulăMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Adăugați a doua regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Adăugați a treia regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Adăugați a patra regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)Sfârșitul Sub

Aceasta utilizează tipul de xlExpression și apoi folosește o formulă Excel standard pentru a determina dacă o celulă este goală în loc de o valoare numerică.

Obiectul FormatConditions face parte din obiectul Range. Acționează în același mod ca o colecție cu indexul începând de la 1. Puteți itera prin acest obiect folosind un Pentru … Următorul sau Pentru … Fiecare buclă.

Ștergerea unei reguli

Uneori, poate fi necesar să ștergeți o regulă individuală dintr-un set de reguli multiple dacă nu corespunde cerințelor de date.

12345678910111213

Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete'Adăugați prima regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)„Șterge regulaMyRange.FormatConditions (1). ȘtergețiSfârșitul Sub

Acest cod creează o nouă regulă pentru intervalul A1: A10, apoi îl șterge. Trebuie să utilizați numărul corect de index pentru ștergere, așa că verificați „Gestionați regulile” din front-end-ul Excel (acesta va afișa regulile în ordinea de execuție) pentru a vă asigura că obțineți numărul corect de index. Rețineți că nu există nici o facilitate de anulare în Excel dacă ștergeți o regulă de formatare condiționată în VBA, spre deosebire de cazul în care o faceți prin front-end-ul Excel.

Schimbarea unei reguli

Deoarece regulile sunt o colecție de obiecte bazate pe un interval specificat, puteți face cu ușurință modificări la anumite reguli folosind VBA. Proprietățile reale odată adăugată regula sunt numai în citire, dar puteți utiliza metoda Modificare pentru a le modifica. Proprietățile precum culorile sunt citite / scrise.

123456789101112131415

Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete'Adăugați prima regulăMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Regula schimbăriiMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10"‘Schimbați culoarea reguliiMyRange.FormatConditions (1) .Interior.Color = vbGreenSfârșitul Sub

Acest cod creează un obiect de gamă (A1: A10) și adaugă o regulă pentru numere între 100 și 150. Dacă condiția este adevărată, atunci culoarea celulei se schimbă în roșu.

Codul schimbă regula în numere mai mici de 10. Dacă condiția este adevărată, atunci culoarea celulei se schimbă acum în verde.

Utilizarea unei scheme de culori gradate

Formatarea condiționată Excel are un mijloc de a utiliza culori gradate pe o gamă de numere care rulează în ordine crescătoare sau descendentă.

Acest lucru este foarte util atunci când aveți date cum ar fi cifrele vânzărilor în funcție de zona geografică, temperaturile orașului sau distanțele dintre orașe. Folosind VBA, aveți avantajul suplimentar de a putea alege propria schemă de culori gradate, mai degrabă decât cele standard oferite pe front-end-ul Excel.

1234567891011121314151617181920212223242526272829

Sub GraduateColors ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete„Definiți tipul scăriiMyRange.FormatConditions.AddColorScale ColorScaleType: = 3„Selectați culoarea pentru cea mai mică valoare din intervalMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueCu MyRange.FormatConditions (1) .ColorScaleCriteria (1). FormatColor.Culoare = 7039480Se termina cu'Selectați culoarea pentru valorile medii din intervalMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Selectați culoarea pentru punctul mediu al gameiCu MyRange.FormatConditions (1) .ColorScaleCriteria (2). FormatColor.Culoare = 8711167Se termina cu„Selectați culoarea pentru cea mai mare valoare din intervalMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHestestValueCu MyRange.FormatConditions (1) .ColorScaleCriteria (3). FormatColor.Culoare = 8109667Se termina cuSfârșitul Sub

Când acest cod este rulat, acesta va gradua culorile celulei în funcție de valorile crescătoare din intervalul A1: A10.

Acesta este un mod foarte impresionant de afișare a datelor și cu siguranță va atrage atenția utilizatorilor.

Formatare condiționată pentru valorile de eroare

Când aveți o cantitate imensă de date, este posibil să pierdeți cu ușurință o valoare de eroare în diferitele foi de lucru. Dacă acest lucru este prezentat unui utilizator fără a fi rezolvat, ar putea duce la probleme mari și utilizatorul își pierde încrederea în cifre. Aceasta utilizează un tip de regulă xlExpression și o funcție Excel a IsError pentru a evalua celula.

Puteți crea cod astfel încât toate celulele cu erori să aibă culoarea roșie a celulei:

1234567891011

Sub ErrorConditionalFormattingExample ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete„Adăugați o regulă de eroareMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Setați culoarea interioară la roșuMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Sfârșitul Sub

Formatare condiționată pentru datele din trecut

Este posibil să aveți date importate acolo unde doriți să evidențiați datele din trecut. Un exemplu în acest sens ar putea fi un raport al debitorilor în care doriți să se evidențieze toate datele vechi de facturare de peste 30 de zile.

Acest cod folosește tipul de regulă xlExpression și o funcție Excel pentru a evalua datele.

1234567891011

Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Creați obiect de gamă pe baza unei coloane de dateSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete'Adăugați o regulă de eroare pentru datele din trecutMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now () - A1> 30"'Setați culoarea interioară la roșuMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Sfârșitul Sub

Acest cod va lua o gamă de date în intervalul A1: A10 și va seta culoarea celulei la roșu pentru orice dată care are peste 30 de zile în trecut.

În formula utilizată în condiție, Now () oferă data și ora curente. Aceasta va continua să recalculeze de fiecare dată când foaia de lucru este recalculată, astfel încât formatarea se va schimba de la o zi la alta.

Utilizarea barelor de date în formatarea condiționată VBA

Puteți utiliza VBA pentru a adăuga bare de date la o serie de numere. Acestea sunt aproape ca mini diagrame și oferă o imagine instantanee a cât de mari sunt numerele unul în raport cu celălalt. Prin acceptarea valorilor implicite pentru barele de date, codul este foarte ușor de scris.

123456

Sub DataBarFormattingExample ()Dim MyRange As RangeSetați MyRange = Range („A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarSfârșitul Sub

Datele dvs. vor arăta astfel în foaia de lucru:

Utilizarea pictogramelor în formatarea condiționată VBA

Puteți utiliza formatarea condiționată pentru a pune pictograme alături de numerele dvs. într-o foaie de lucru. Pictogramele pot fi săgeți sau cercuri sau diverse alte forme. În acest exemplu, codul adaugă pictograme săgeată la numere pe baza valorilor procentuale ale acestora:

12345678910111213141516171819202122232425

Sub IconSetsExample ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete'Adăugați set de pictograme la obiectul FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Setați pictograma setată la săgeți - condiția 1Cu MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Se termina cu'setați criteriile pictogramei pentru procentajul necesar - condiția 2Cu MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Valoare = 33.Operator = xlGreaterEqualSe termina cu'setați criteriile pictogramei pentru procentajul necesar - condiția 3Cu MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Valoare = 67.Operator = xlGreaterEqualSe termina cuSfârșitul Sub

Aceasta va oferi o vizualizare instantanee care arată dacă un număr este mare sau scăzut. După rularea acestui cod, foaia dvs. de lucru va arăta astfel:

Utilizarea formatării condiționate pentru a evidenția primele cinci

Puteți utiliza codul VBA pentru a evidenția primele 5 numere dintr-un interval de date. Utilizați un parametru numit „AddTop10”, dar puteți ajusta numărul de rang din cod la 5. Un utilizator poate dori să vadă cele mai mari numere dintr-un interval fără a fi nevoie să sorteze mai întâi datele.

1234567891011121314151617181920212223

Sub Top5Example ()Dim MyRange As Range'Creați obiect de gamăSetați MyRange = Range („A1: A10”)'Ștergeți formatele condiționale anterioareMyRange.FormatConditions.Delete'Adăugați o condiție Top10MyRange.FormatConditions.AddTop10Cu MyRange.FormatConditions (1)'Setați parametrul de sus în jos.TopBottom = xlTop10Top'Setați doar top 5.Rang = 5Se termina cuCu MyRange.FormatConditions (1) .Font'Setați culoarea fontului.Culoare = -16383844Se termina cuCu MyRange.FormatConditions (1) .Interior'Setați culoarea fundalului celulei.Culoare = 13551615Se termina cuSfârșitul Sub

Datele din foaia dvs. de lucru ar arăta astfel după executarea codului:

Rețineți că valoarea 145 apare de două ori, astfel încât sunt evidențiate șase celule.

Semnificația parametrilor StopIfTrue și SetFirstPriority

StopIfTrue este important dacă o gamă de celule are mai multe reguli de formatare condiționată. O singură celulă din interval poate satisface prima regulă, dar poate satisface și regulile ulterioare. Ca dezvoltator, vă recomandăm să afișeze formatarea numai pentru prima regulă la care ajunge. Alte criterii de regulă se pot suprapune și pot face modificări neintenționate dacă li se permite să continue lista de reguli.

Valoarea implicită a acestui parametru este True, dar îl puteți modifica dacă doriți să se ia în considerare toate celelalte reguli pentru acea celulă:

1	MyRange. FormatConditions (1) .StopIfTrue = False

Parametrul SetFirstPriority dictează dacă regula condiției va fi evaluată mai întâi atunci când există mai multe reguli pentru acea celulă.

1	MyRange. FormatConditions (1) .SetFirstPriority

Aceasta mută poziția acelei reguli în poziția 1 în cadrul colecției de condiții de format și orice alte reguli vor fi mutate în jos cu modificarea numerelor de index. Aveți grijă dacă efectuați modificări ale regulilor din cod folosind numerele index. Trebuie să vă asigurați că modificați sau ștergeți regula corectă.

Puteți modifica prioritatea unei reguli:

1	MyRange. FormatConditions (1). Prioritate = 3

Aceasta va schimba pozițiile relative ale oricăror alte reguli din lista de formate condiționate.

Utilizarea formatării condiționate care face referire la alte valori ale celulei

Acesta este un lucru pe care formatarea condiționată Excel nu o poate face. Cu toate acestea, puteți face propriul cod VBA pentru a face acest lucru.

Să presupunem că aveți o coloană de date și, în celula adiacentă fiecărui număr, există un text care indică ce formatare ar trebui să aibă loc pe fiecare număr.

Următorul cod vă va scurge lista de numere, va căuta în celula alăturată pentru formatarea textului și apoi va format formatul după cum este necesar:

123456789101112131415161718192021

Sub ReferToAnotherCellForConditionalFormatting ()'Creați variabile pentru a menține numărul de rânduri pentru datele tabulareDim RRow As Long, N At Long„Capturați numărul de rânduri din intervalul de date tabulareRRow = ActiveSheet.UsedRange.Rows.Count„Repetați toate rândurile din intervalul de date tabularePentru N = 1 la rând„Utilizați o declarație Select Case pentru a evalua formatarea pe baza coloanei 2Selectați Case ActiveSheet.Cells (N, 2). Valoare'Transformați culoarea interioară în albastruCaz „albastru”ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Transformați culoarea interioară în roșuCarcasă „roșie”ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Transformați culoarea interioară în verdeCaz „verde”ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenSelectare sfârșitUrmătorul NSfârșitul Sub

Odată ce acest cod a fost rulat, foaia dvs. de lucru va arăta astfel:

Celulele la care se face referire pentru formatare ar putea fi oriunde pe foaia de lucru sau chiar pe o altă foaie de lucru din registrul de lucru. Puteți utiliza orice formă de text pentru a crea o condiție pentru formatare și sunteți limitat doar de imaginația dvs. în utilizările la care ați putea pune acest cod.

Operatori care pot fi utilizați în Declarații de formatare condiționată

După cum ați văzut în exemplele anterioare, operatorii sunt utilizați pentru a determina modul în care valorile condiției vor fi evaluate, de exemplu xlIntre.

Există o serie de acești operatori care pot fi utilizați, în funcție de modul în care doriți să specificați criteriile de regulă.

Nume	Valoare	Descriere
xlIntre	1	Între. Poate fi utilizat numai dacă sunt furnizate două formule.
xlEqual	3	Egal.
xlMai mare	5	Mai mare ca.
xlGreaterEqual	7	Mai mare sau egal cu.
xlLess	6	Mai puțin decât.
xlLessEqual	8	Mai mic sau egal cu.
xlNotBetween	2	Nu între. Poate fi utilizat numai dacă sunt furnizate două formule.
xlNotEqual	4	Nu este egal.