Betinget VBA-formatering - Anvend betinget format ved hjælp af VBA Excel

Betinget formatering i Excel VBA

Vi kan anvende betinget formatering til en celle eller et celleområde i Excel. Et betinget format er et format, der kun anvendes på celler, der opfylder bestemte kriterier, siger værdier over en bestemt værdi, positive eller negative værdier eller værdier med en bestemt formel osv. Denne betingede formatering kan også udføres i excel VBA-programmering ved hjælp af ' Formatering af samlingen af ​​betingelser ' i makroen / proceduren.

Formattilstand bruges til at repræsentere et betinget format, der kan indstilles ved at kalde en metode, der returnerer en variabel af den type. Den indeholder alle betingede formater for et enkelt interval og kan kun indeholde tre formatbetingelser.

FormatConditions.Add / Modify / Delete bruges i VBA til at tilføje / ændre / slette FormatCondition-objekter til samlingen. Hvert format er repræsenteret af et FormatCondition-objekt. FormatConditions er en egenskab for Range-objektet, og tilføj følgende parametre med nedenstående syntaks:

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

Syntaksen for Tilføj formel har følgende argumenter:

  • Type: Påkrævet, repræsenterer hvis det betingede format er baseret på værdien i cellen eller et udtryk.
  • Operator: Valgfri, repræsenterer operatøren, der skal bruges med en værdi, når 'Type' er baseret på celleværdien.
  • Formel1: Valgfri, repræsenterer den værdi eller det udtryk, der er knyttet til det betingede format.
  • Formel2: Valgfri, repræsenterer den værdi eller det udtryk, der er knyttet til den anden del af det betingede format, når parameteren: 'Operatør' enten er 'xlBetween' eller 'xlNotBetween.'

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

Følgende er listen over nogle værdier / enumeration, der kan tages af nogle parametre for 'Add' / 'Modify':

Eksempler på VBA betinget formatering

Nedenfor er eksemplerne på betinget formatering i excel VBA.

Eksempel nr. 1

Lad os sige, at vi har en Excel-fil, der indeholder nogle studerendes navn og karakterer, og vi ønsker at bestemme / fremhæve karaktererne som fed og blå i farve, som er større end 80, og som fed og rød i farve, som er mindre end 50. Lad os se dataene i filen:

Vi bruger FormatConditions.Tilføj funktionen som nedenfor for at opnå dette:

  • Gå til Developer -> Visual Basic Editor:
  • Højreklik på navnet på projektmappen i ruden 'Project-VBAProject' -> 'Indsæt' -> 'Modul.'
  • Skriv nu koden / proceduren i dette modul:

Kode:

Underformatering () Afslut sub
  • Definer variablen rng, condition1, condition2:

Kode:

Underformatering () Dim rng Som område Dim tilstand1 Som FormatCondition, tilstand2 Som FormatCondition End Sub
  • Indstil / rett det område, hvor betinget formatering skal ønskes ved hjælp af VBA 'Range' -funktionen:

Kode:

Underformatering () Dim rng Som område Dim-tilstand1 Som FormatCondition, condition2 Som FormatCondition Indstil rng = Range ("B2", "B11") Afslut sub
  • Slet / ryd enhver eksisterende betinget formatering (hvis nogen) fra området ved hjælp af 'FormatConditions.Delete':

Kode:

Underformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Slet End Sub
  • Definer og indstil nu kriterierne for hvert betinget format ved hjælp af '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 ") Indstil betingelse2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") Afslut sub
  • Definer og indstil det format, der skal anvendes til hver betingelse

Kopier og indsæt denne kode i dit VBA-klassemodul.

Kode:

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

Når vi nu kører denne kode ved hjælp af F5-tasten eller manuelt, ser vi, at de mærker, der er mindre end 50, bliver fremhævet med fed og rød, mens de, der er større end 80, fremhæves med fed og blå som følger:

Bemærk: Nogle af egenskaberne til udseendet af formaterede celler, der kan bruges med FormatCondition, er:

Eksempel 2

Lad os sige, at vi i ovenstående eksempel også har en anden kolonne, der siger, at den studerende er en 'Topper', hvis han / hun scorer mere end 80 karakterer, ellers bestås / ikke bestået. Nu ønsker vi at fremhæve de værdier, der er angivet som 'Topper' som fed og blå. Lad os se dataene i filen:

I dette tilfælde fungerer koden / proceduren som følger:

Kode:

Sub TextFormatting () Afslut Sub

Definer og indstil det format, der skal anvendes til hver betingelse

Kode:

Undertekstformatering () med rækkevidde ("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 ovenstående kode, at vi ønsker at teste, om området: 'C2: C11' indeholder strengen: "Topper", så parameteren: "Onamestor" i 'Format.Add' tager optællingen: "Xcontains" til test denne betingelse i det faste område (dvs. C2: C11), og udfør derefter den krævede betingede formatering (skrifttypeskift) på dette interval.

Når vi nu kører denne kode manuelt eller ved at trykke på F5-tasten, ser vi, at celleværdier med 'Topper' bliver fremhævet i blå og fed:

Bemærk: Så vi har set i ovenstående to eksempler, hvordan 'Tilføj' -metoden fungerer i tilfælde af nogen celleværdikriterier (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 at anvende mere end tre betingede formater til et interval ved hjælp af 'Tilføj' -metoden, kan vi bruge 'Hvis' eller 'vælg sag.'
  • Hvis 'Tilføj' -metoden har sin 'Type' -parameter som: 'xlExpression', ignoreres parameteren 'Operator'.
  • Parametrene: 'Formel1' og 'Formel2' i 'Tilføj' -metoden kan være en cellereference, konstant værdi, strengværdi eller endda en formel.
  • Parameteren: 'Formula2' bruges kun, når parameteren: 'Operator' enten er 'xlBetween' eller 'xlNotBetween', ellers ignoreres den.
  • For at fjerne al betinget formatering fra ethvert regneark kan vi bruge metoden 'Slet' som følger:
Cells.FormatConditions.Slet

Interessante artikler...