Formatare condiționată VBA - Aplicați format condiționat utilizând VBA Excel

Formatare condiționată în Excel VBA

Putem aplica formatarea condiționată unei celule sau unei game de celule din Excel. Un format condițional este un format care se aplică numai celulelor care îndeplinesc anumite criterii, de exemplu valori peste o anumită valoare, valori pozitive sau negative sau valori cu o anumită formulă etc. Această formatare condiționată se poate face și în programarea VBA excel „ Colectarea condițiilor de formatare ” din macro / procedură.

Format Condition este utilizat pentru a reprezenta un format condițional care poate fi setat apelând o metodă care returnează o variabilă de acel tip. Conține toate formatele condiționate pentru o singură gamă și poate deține doar trei condiții de format.

FormatConditions.Add / Modify / Delete este utilizat în VBA pentru a adăuga / modifica / șterge obiecte FormatCondition la colecție. Fiecare format este reprezentat de un obiect FormatCondition. FormatConditions este o proprietate a obiectului Range și adăugați următorii parametri cu sintaxa de mai jos:

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

Sintaxa Adăugare formulă are următoarele argumente:

  • Tip: Obligatoriu, reprezintă dacă formatul condițional se bazează pe valoarea prezentă în celulă sau o expresie.
  • Operator: Opțional, reprezintă operatorul care trebuie utilizat cu o valoare atunci când „Tip” se bazează pe valoarea celulei.
  • Formula1: opțional, reprezintă valoarea sau expresia asociată cu formatul condițional.
  • Formula2: Opțional, reprezintă valoarea sau expresia asociată cu a doua parte a formatului condițional atunci când parametrul: „Operator” este fie „xlBetween”, fie „xlNotBetween”.

FormatConditions.Modify are, de asemenea, aceeași sintaxă ca FormatConditions.Add.

Următoarea listă a unor valori / enumerări care pot fi luate de unii parametri din „Adăugare” / „Modificare”:

Exemple de formatare condiționată VBA

Mai jos sunt exemple de formatare condiționată în Excel VBA.

Exemplul nr. 1

Să spunem că avem un fișier Excel care conține numele și semnele unor elevi și dorim să determinăm / evidențiați semnele ca Bold și albastru, care este mai mare de 80, și ca Bold și roșu, care este mai mic decât 50. Să vedem datele conținute în fișier:

Folosim FormatConditions.Adăugați funcția după cum urmează pentru a realiza acest lucru:

  • Accesați Developer -> Visual Basic Editor:
  • Faceți clic dreapta pe numele registrului de lucru în panoul „Project-VBAProject ”->„ Insert ”->„ Module ”.
  • Acum scrieți codul / procedura în acest modul:

Cod:

Formatare secundară () Finalizare sub
  • Definiți variabila rng, condiție1, condiție2:

Cod:

Sub formatare () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition End Sub
  • Setați / fixați intervalul pe care se dorește formatarea condiționată utilizând funcția VBA „Range”:

Cod:

Formatare secundară () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") End Sub
  • Ștergeți / ștergeți orice formatare condițională existentă (dacă există) din interval, utilizând „FormatConditions.Delete”:

Cod:

Formatare secundară () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Elete End Sub
  • Acum definiți și setați criteriile pentru fiecare format condițional, utilizând „FormatConditions.Add”:

Cod:

Sub formatting () 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 ") Set condition2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub
  • Definiți și setați formatul care trebuie aplicat pentru fiecare condiție

Copiați și lipiți acest cod în modulul dvs. de clasă VBA.

Cod:

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

Acum, când rulăm acest cod folosind tasta F5 sau manual, vedem că marcajele mai mici de 50 sunt evidențiate cu aldine și roșii, în timp ce cele mai mari de 80 sunt evidențiate cu aldine și albastre după cum urmează:

Notă: Unele dintre proprietățile pentru aspectul celulelor formatate care pot fi utilizate cu FormatCondition sunt:

Exemplul nr. 2

Să spunem că în exemplul de mai sus avem și o altă coloană care afirmă că elevul este un „Topper” dacă el / ea înregistrează mai mult de 80 de puncte, altfel Pass / Fail scris împotriva lor. Acum dorim să evidențiem valorile declarate ca „Topper” ca Bold și Blue. Să vedem datele conținute în fișier:

În acest caz, codul / procedura ar funcționa după cum urmează:

Cod:

Sub TextFormatting () End Sub

Definiți și setați formatul care trebuie aplicat pentru fiecare condiție

Cod:

Sub TextFormatting () With Range ("c2: c11"). FormatConditions.Add (xlTextString, TextOperator: = xlContains, String: = "topper") With .Font .Bold = True .Color = vbBlue End With End With End Sub

Putem vedea în codul de mai sus că dorim să testăm dacă intervalul: „C2: C11” conține șirul: „Topper”, deci parametrul: „Onamestor” din „Format.Add” ia enumerarea: „Xcontains” la testați această condiție în intervalul fix (adică, C2: C11), apoi efectuați formatarea condițională necesară (modificări ale fontului) pe acest interval.

Acum, când rulăm acest cod manual sau apăsând tasta F5, vedem că valorile celulei cu „Topper” sunt evidențiate în albastru și aldin:

Notă: Deci, am văzut în cele două exemple de mai sus cum funcționează metoda „Adăugați” în cazul oricărui criteriu de valoare a celulei (șir numeric sau text).

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.
  • Pentru a aplica mai mult de trei formate condiționale unui interval folosind metoda „Adăugați”, putem folosi „Dacă” sau „selectați caz”.
  • Dacă metoda „Adăugați” are parametrul „Tip” ca: „xlExpression”, atunci parametrul „Operator” este ignorat.
  • Parametrii: „Formula1” și „Formula2” din metoda „Adăugați” pot fi o referință de celulă, valoare constantă, valoare șir sau chiar o formulă.
  • Parametrul: „Formula2” este utilizat numai atunci când parametrul: „Operator” este „xlBetween” sau „xlNotBetween”, altfel este ignorat.
  • Pentru a elimina toate formatările condiționate din orice foaie de lucru, putem folosi metoda „Ștergeți” după cum urmează:
Cells.FormatConditions.Elete

Articole interesante...