Betinget formatering av VBA - Bruk betinget format ved hjelp av VBA Excel

Betinget formatering i Excel VBA

Vi kan bruke betinget formatering til en celle eller et celleområde i Excel. Et betinget format er et format som bare brukes på celler som oppfyller visse kriterier, si verdier over en bestemt verdi, positive eller negative verdier, eller verdier med en bestemt formel, etc. Denne betingede formateringen kan også gjøres i excel VBA-programmering ved å bruke ' Format betingelser samling ' i makroen / prosedyren.

Formattilstand brukes til å representere et betinget format som kan angis ved å ringe en metode som returnerer en variabel av den typen. Den inneholder alle betingede formater for et enkelt område og kan bare inneholde tre formatforhold.

FormatConditions.Add / Modify / Delete brukes i VBA for å legge til / modifisere / slette FormatCondition-objekter til samlingen. Hvert format er representert av et FormatCondition-objekt. FormatConditions er en egenskap for Range-objektet, og legg til følgende parametere med syntaksen nedenfor:

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

Syntaksen Legg til formel har følgende argumenter:

  • Type: Påkrevd, representerer hvis det betingede formatet er basert på verdien som er tilstede i cellen eller et uttrykk.
  • Operatør: Valgfritt, representerer operatøren som skal brukes med en verdi når 'Type' er basert på celleverdien.
  • Formel1: Valgfritt, representerer verdien eller uttrykket som er knyttet til det betingede formatet.
  • Formel2: Valgfritt, representerer verdien eller uttrykket som er knyttet til den andre delen av betinget format når parameteren: 'Operatør' enten er 'xlBetween' eller 'xlNotBetween.'

FormatConditions.Modify har også samme syntaks som FormatConditions.Add.

Følgende er listen over noen verdier / oppregning som kan tas av noen parametere for 'Legg til' / 'Endre':

Eksempler på VBA betinget formatering

Nedenfor er eksemplene på betinget formatering i excel VBA.

Eksempel 1

La oss si at vi har en Excel-fil som inneholder noen studenters navn og karakterer, og vi ønsker å bestemme / markere merkene som fet og blå i fargen, som er større enn 80, og som fet og rød i fargen, som er mindre enn 50. La oss se dataene i filen:

Vi bruker FormatConditions.Legg til funksjonen som nedenfor for å oppnå dette:

  • Gå til utvikler -> Visual Basic Editor:
  • Høyreklikk på arbeidsboknavnet i 'Project-VBAProject' -ruten-> 'Sett inn' -> 'Modul.'
  • Skriv nå koden / prosedyren i denne modulen:

Kode:

Subformatering () Slutt Sub
  • Definer variabelen rng, condition1, condition2:

Kode:

Subformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition End Sub
  • Angi / fikse området som betinget formatering er ønsket med VBA 'Range' -funksjon:

Kode:

Underformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition Set rng = Range ("B2", "B11") End Sub
  • Slett / fjern eventuell eksisterende betinget formatering (hvis noen) fra området ved hjelp av 'FormatConditions.Delete':

Kode:

Subformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Slette End Sub
  • Definere og angi kriteriene for hvert betinget format ved hjelp av 'FormatConditions.Add':

Kode:

Underformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Set condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Sett condition2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub
  • Definer og angi formatet som skal brukes for hver tilstand

Kopier og lim inn denne koden i VBA-klassemodulen.

Kode:

Underformatering () 'Definere variablene: Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition' Fixing / Setting the range on which conditional formatting is to wish Set rng = Range ("B2", "B11") 'To slett / tøm all eksisterende betinget formatering fra området rng.FormatConditions.Delete 'Definere og sette kriteriene for hvert betinget format Sett condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Angi condition2 = rng.FormatConditions. Legg til (xlCellValue, xlLess, "= 50") 'Definere og angi formatet som skal brukes for hver tilstand Med tilstand1 .Font.Color = vbBlue .Font.Bold = True End With With condition2 .Font.Color = vbRed .Font. Fet = True End With End Sub

Nå når vi kjører denne koden ved hjelp av F5-tasten eller manuelt, ser vi at merkene som er mindre enn 50 blir uthevet med fet skrift og rødt, mens de som er større enn 80 blir uthevet med fet skrift og blått som følger:

Merk: Noen av egenskapene for utseendet på formaterte celler som kan brukes med FormatCondition er:

Eksempel 2

La oss si at i eksemplet ovenfor har vi også en annen kolonne som sier at studenten er en 'Topper' hvis han / hun scorer mer enn 80 poeng, ellers bestås / ikke bestått mot dem. Nå ønsker vi å markere verdiene som er angitt som 'Topper' som Fet og Blå. La oss se dataene i filen:

I dette tilfellet vil koden / prosedyren fungere som følger:

Kode:

Sub TextFormatting () Slutt Sub

Definer og angi formatet som skal brukes for hver tilstand

Kode:

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

Vi kan se i koden ovenfor at vi ønsker å teste om området: 'C2: C11' inneholder strengen: "Topper", så parameteren: "Onamestor" i "Format.Add" tar oppregningen: "Xcontains" til test denne tilstanden i det faste området (dvs. C2: C11), og gjør deretter den nødvendige betingede formateringen (skriftendringer) på dette området.

Nå når vi kjører denne koden manuelt eller ved å trykke på F5-tasten, ser vi at celleverdier med 'Topper' blir uthevet i blått og fet skrift:

Merk: Så, vi har sett i de to ovennevnte eksemplene hvordan 'Legg til' -metoden fungerer i tilfelle noen celleverdikriterier (numerisk eller tekststreng).

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.
  • For å bruke mer enn tre betingede formater til et område ved hjelp av 'Legg til' -metoden, kan vi bruke 'Hvis' eller 'velg bokstav.'
  • Hvis 'Legg til' metoden har sin 'Type' parameter som: 'xlExpression', blir parameteren 'Operator' ignorert.
  • Parametrene: 'Formula1' og 'Formula2' i 'Add' -metoden kan være en cellereferanse, konstant verdi, strengverdi eller til og med en formel.
  • Parameteren: 'Formula2' brukes bare når parameteren: 'Operator' enten er 'xlBetween' eller 'xlNotBetween', ellers blir den ignorert.
  • For å fjerne all betinget formatering fra et regneark, kan vi bruke 'Slett' -metoden som følger:
Cells.FormatConditions.Slett

Interessante artikler...