Bruke betinget formatering med Excel VBA

Excel betinget formatering

Excel betinget formatering lar deg definere regler som bestemmer celleformatering.

For eksempel kan du opprette en regel som fremhever celler som oppfyller visse kriterier. Eksempler inkluderer:

• Tall som faller innenfor et bestemt område (f.eks. Mindre enn 0).
• De 10 beste elementene i en liste.
• Lag et "varmekart".
• "Formelbaserte" regler for praktisk talt enhver betinget formatering.

I Excel finner du betinget formatering på båndet under Hjem> Stiler (ALT> H> L).

For å lage din egen regel, klikk på 'Ny regel', og et nytt vindu vises:

Betinget formatering i VBA

Alle disse funksjonene for betinget formatering kan nås med VBA.

Vær oppmerksom på at når du konfigurerer betinget formatering fra VBA-koden, vil de nye parameterne vises i vinduet for betinget formatering i Excel front-end og være synlige for brukeren. Brukeren vil kunne redigere eller slette disse med mindre du har låst regnearket.

De betingede formateringsreglene lagres også når regnearket lagres

Betingede formateringsregler gjelder spesielt for et bestemt regneark og for et bestemt celleområde. Hvis de trengs andre steder i arbeidsboken, må de også være satt opp på regnearket.

Praktiske bruksområder for betinget formatering i VBA

Du kan ha en stor del rå data importert til regnearket fra en CSV-fil (kommaadskilte verdier), eller fra en databasetabell eller spørring. Dette kan flyte gjennom til et dashbord eller en rapport, med endrede tall importert fra en periode til en annen.

Der et tall endres og ligger utenfor et akseptabelt område, kan det være lurt å markere dette f.eks. bakgrunnsfargen på cellen i rødt, og du kan gjøre dette ved å sette opp betinget formatering. På denne måten vil brukeren umiddelbart bli tiltrukket av dette nummeret, og kan deretter undersøke hvorfor dette skjer.

Du kan bruke VBA til å slå den betingede formateringen på eller av. Du kan bruke VBA til å fjerne reglene for en rekke celler, eller slå dem på igjen. Det kan være en situasjon der det er en helt god grunn til et uvanlig antall, men når brukeren presenterer dashbordet eller rapporterer til et høyere ledelsesnivå, vil de kunne fjerne "alarmklokkene".

Også på de rå importerte dataene vil du kanskje markere hvor tallene er latterlig store eller latterlig små. Det importerte dataområdet er vanligvis en annen størrelse for hver periode, så du kan bruke VBA til å evaluere størrelsen på det nye dataområdet og sette inn betinget formatering bare for det området.

Du kan også ha en situasjon der det er en sortert liste med navn med numeriske verdier mot hver enkelt f.eks. ansattes lønn, eksamenskarakterer. Med betinget formatering kan du bruke graderte farger for å gå fra høyeste til laveste, noe som ser veldig imponerende ut for presentasjonsformål.

Listen over navn vil imidlertid ikke alltid være statisk i størrelse, og du kan bruke VBA -kode til å oppdatere skalaen til graderte farger i henhold til endringer i størrelsen på området.

Et enkelt eksempel på å lage et betinget format på et område

Dette eksemplet setter opp betinget formatering for et celleområde (A1: A10) i et regneark. Hvis tallet i området er mellom 100 og 150, vil cellebakgrunnsfargen være rød, ellers vil den ikke ha noen farge.

1234567891011121314

Sub ConditionalFormattingExample ()'Definer rekkeviddeDim MyRange As RangeSett MyRange = Range (“A1: A10”)'Slett eksisterende betinget formatering fra områdeMyRange.FormatConditions.Delete'Bruk betinget formateringMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Slutt Sub

Legg merke til at vi først definerer området MyRange for å bruke betinget formatering.

Deretter sletter vi eksisterende betinget formatering for området. Dette er en god idé for å forhindre at den samme regelen legges til hver gang koden kjøres (selvfølgelig vil det ikke være hensiktsmessig under alle omstendigheter).

Farger er gitt med numeriske verdier. Det er en god idé å bruke RGB (rød, grønn, blå) notasjon for dette. Du kan bruke standard fargekonstanter for dette f.eks. vbRed, vbBlue, men du er begrenset til åtte fargevalg.

Det er over 16,7 millioner farger tilgjengelig, og ved hjelp av RGB kan du få tilgang til dem alle. Dette er langt lettere enn å prøve å huske hvilket tall som passer med hvilken farge. Hvert av de tre RGB -fargetallene er fra 0 til 255.

Vær oppmerksom på at parameteren ‘xlBetween’ er inkluderende, slik at celleverdier på 100 eller 150 tilfredsstiller betingelsen.

Formatering med flere betingelser

Det kan være lurt å sette opp flere betingede regler i dataområdet ditt slik at alle verdiene i et område dekkes av forskjellige forhold:

12345678910111213141516171819

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range (“A1: A10”)'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til den første regelenMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Legg til andre regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formel1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Legg til tredje regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formel1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbGulSlutt Sub

Dette eksemplet setter opp den første regelen som før, med cellefargen rød hvis celleverdien er mellom 100 og 150.

Ytterligere to regler blir deretter lagt til. Hvis celleverdien er mindre enn 100, er cellefargen blå, og hvis den er større enn 150, er cellefargen gul.

I dette eksemplet må du sikre at alle muligheter for tall er dekket, og at reglene ikke overlapper hverandre.

Hvis tomme celler er i dette området, vil de vises som blå, fordi Excel fortsatt tar dem som en verdi mindre enn 100.

Veien rundt dette er å legge til en annen tilstand som et uttrykk. Dette må legges til som den første betingelsesregelen i koden. Det er veldig viktig der det er flere regler, for å få kjøringsrekkefølgen riktig ellers kan resultatene være uforutsigbare.

1234567891011121314151617181920212223

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range (“A1: A10”)'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til den første regelenMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LENG (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Legg til andre regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Legg til tredje regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formel1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Legg til fjerde regelMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formel1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)Slutt Sub

Dette bruker typen xlExpression, og bruker deretter en standard Excel -formel for å avgjøre om en celle er tom i stedet for en numerisk verdi.

FormatConditions -objektet er en del av Range -objektet. Det fungerer på samme måte som en samling med indeksen som starter på 1. Du kan iterere gjennom dette objektet ved å bruke en For… Next eller For… Every loop.

Sletter en regel

Noen ganger må du kanskje slette en individuell regel i et sett med flere regler hvis den ikke passer til datakravene.

12345678910111213

Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range ("A1: A10")'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til den første regelenMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Slett regelenMyRange.FormatConditions (1). SlettSlutt Sub

Denne koden oppretter en ny regel for område A1: A10, og sletter den deretter. Du må bruke riktig indeksnummer for slettingen, så sjekk "Administrer regler" på Excel-grensesnittet (dette vil vise reglene i rekkefølge for utførelse) for å sikre at du får riktig indeksnummer. Vær oppmerksom på at det ikke er angrer i Excel hvis du sletter en betinget formateringsregel i VBA, i motsetning til hvis du gjør det gjennom Excel-grensesnittet.

Endring av en regel

Fordi reglene er en samling objekter basert på et spesifisert område, kan du enkelt gjøre bestemte regler ved å bruke VBA. De faktiske egenskapene når regelen er lagt til, er skrivebeskyttet, men du kan bruke metoden Endre til å endre dem. Egenskaper som farger leses / skrives.

123456789101112131415

Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range (“A1: A10”)'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til den første regelenMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Endre regelMyRange.FormatConditions (1) .Modifiser xlCellValue, xlLess, "10"'Endre regelfargeMyRange.FormatConditions (1) .Interior.Color = vbGreenSlutt Sub

Denne koden oppretter et områdeobjekt (A1: A10) og legger til en regel for tall mellom 100 og 150. Hvis betingelsen er sann, endres cellefargen til rød.

Koden endrer deretter regelen til tall mindre enn 10. Hvis betingelsen er sann, endres cellefargen nå til grønt.

Bruke et fargeskjema

Betinget formatering i Excel har et middel til å bruke graderte farger på en rekke tall som løper i stigende eller synkende rekkefølge.

Dette er veldig nyttig der du har data som salgstall etter geografisk område, bytemperaturer eller avstander mellom byer. Ved å bruke VBA har du den ekstra fordelen ved å kunne velge din egen fargeskjema i stedet for de vanlige som tilbys på Excel-grensesnittet.

1234567891011121314151617181920212223242526272829

Sub GraduatedColors ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range (“A1: A10”)'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Definer skala typeMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Velg farge for den laveste verdien i områdetMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueMed MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Farge = 7039480Slutt med'Velg farge for de midterste verdiene i områdetMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Velg fargen for midtpunktet i områdetMed MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Farge = 8711167Slutt med'Velg farge for den høyeste verdien i områdetMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueMed MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Farge = 8109667Slutt medSlutt Sub

Når denne koden kjøres, vil den gradere cellefargene i henhold til de stigende verdiene i området A1: A10.

Dette er en veldig imponerende måte å vise dataene på og vil sikkert fange brukernes oppmerksomhet.

Betinget formatering for feilverdier

Når du har en enorm mengde data, kan du lett gå glipp av en feilverdi i de forskjellige regnearkene. Hvis dette blir presentert for en bruker uten å bli løst, kan det føre til store problemer og brukeren mister tilliten til tallene. Dette bruker en regeltype xlExpression og en Excel -funksjon av IsError for å evaluere cellen.

Du kan opprette kode slik at alle celler med feil i har en cellefarge av rødt:

1234567891011

Sub ErrorConditionalFormattingExample ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range ("A1: A10")'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til feilregelMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Sett innvendig farge til rødMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Slutt Sub

Betinget formatering for tidligere datoer

Du kan ha data importert der du vil markere datoer som er tidligere. Et eksempel på dette kan være en debitorrapport hvor du vil at gamle fakturadatoer som er over 30 dager gamle skal skille seg ut.

Denne koden bruker regeltypen xlExpression og en Excel -funksjon for å evaluere datoene.

1234567891011

Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Lag rekkeviddeobjekt basert på en kolonne med datoerSett MyRange = Range ("A1: A10")'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til feilregel for tidligere datoerMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now ()-A1> 30"'Sett innvendig farge til rødMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Slutt Sub

Denne koden vil ta en rekke datoer i området A1: A10, og vil sette cellefargen til rød for enhver dato som er over 30 dager tidligere.

I formelen som brukes i tilstanden, gir Now () nåværende dato og klokkeslett. Dette vil fortsette å regne ut hver gang regnearket blir beregnet på nytt, så formateringen vil endres fra en dag til den neste.

Bruke databaner i VBA betinget formatering

Du kan bruke VBA til å legge til datalinjer i en rekke tall. Disse er nesten som mini -diagrammer, og gir en umiddelbar oversikt over hvor store tallene er i forhold til hverandre. Ved å godta standardverdier for datalinjene, er koden veldig lett å skrive.

123456

Sub DataBarFormattingExample ()Dim MyRange As RangeSett MyRange = Range (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarSlutt Sub

Dataene dine vil se slik ut på regnearket:

Bruke ikoner i VBA betinget formatering

Du kan bruke betinget formatering til å sette ikoner ved siden av tallene dine i et regneark. Ikonene kan være piler eller sirkler eller forskjellige andre former. I dette eksemplet legger koden til pilikoner til tallene basert på prosentverdiene:

12345678910111213141516171819202122232425

Sub IconSetsExample ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range (“A1: A10”)'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til ikonsett i FormatConditions -objektetMyRange.FormatConditions.AddIconSetCondition'Sett ikonet til piler - betingelse 1Med MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Slutt med'angi ikonkriteriene for den nødvendige prosentverdien - betingelse 2Med MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercentVerdi = 33.Operator = xlGreaterEqualSlutt med'angi ikonkriteriene for den nødvendige prosentverdien - betingelse 3Med MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercentVerdi = 67.Operator = xlGreaterEqualSlutt medSlutt Sub

Dette vil gi en umiddelbar visning som viser om et tall er høyt eller lavt. Etter at du har kjørt denne koden, vil regnearket ditt se slik ut:

Bruke betinget formatering for å markere de fem beste

Du kan bruke VBA -kode til å markere de fem beste tallene i et dataområde. Du bruker en parameter som heter ‘AddTop10’, men du kan justere rangnummeret i koden til 5. En bruker vil kanskje se de høyeste tallene i et område uten å måtte sortere dataene først.

1234567891011121314151617181920212223

Sub Top5Example ()Dim MyRange As Range'Lag rekkeviddeobjektSett MyRange = Range (“A1: A10”)'Slett tidligere betingede formaterMyRange.FormatConditions.Delete'Legg til en Top10 -tilstandMyRange.FormatConditions.AddTop10Med MyRange.FormatConditions (1)'Sett parameteren topp til bunn.TopBottom = xlTop10Top'Bare topp 5.Rang = 5Slutt medMed MyRange.FormatConditions (1) .Font'Angi skriftfarge.Color = -16383844Slutt medMed MyRange.FormatConditions (1) .Interiør'Still inn bakgrunnsfargen på cellen.Farge = 13551615Slutt medSlutt Sub

Dataene i regnearket ditt vil se slik ut etter å ha kjørt koden:

Vær oppmerksom på at verdien på 145 vises to ganger, så seks celler er uthevet.

Betydningen av parametrene StopIfTrue og SetFirstPriority

StopIfTrue er viktig hvis en rekke celler har flere betingede formateringsregler. En enkelt celle innenfor området kan tilfredsstille den første regelen, men den kan også tilfredsstille påfølgende regler. Som utvikler vil du kanskje at den bare skal vise formateringen for den første regelen den kommer til. Andre regelkriterier kan overlappe hverandre og kan gjøre utilsiktede endringer hvis de får lov til å fortsette nedover listen over regler.

Standard på denne parameteren er True, men du kan endre den hvis du vil at alle de andre reglene for den cellen skal vurderes:

1	MyRange. FormatConditions (1) .StopIfTrue = False

Parameteren SetFirstPriority dikterer om denne betingelsesregelen først skal evalueres når det er flere regler for den cellen.

1	MyRange. FormatConditions (1) .SetFirstPriority

Dette flytter posisjonen til den regelen til posisjon 1 i samlingen av formatvilkår, og eventuelle andre regler flyttes nedover med endrede indeksnummer. Vær forsiktig hvis du gjør noen endringer i regler i koden ved hjelp av indeksnumrene. Du må sørge for at du endrer eller sletter den riktige regelen.

Du kan endre prioriteten til en regel:

1	MyRange. FormatConditions (1) .Prioritet = 3

Dette vil endre den relative posisjonen til andre regler i listen over betingede format.

Bruke betinget formatering med henvisning til andre celleverdier

Dette er en ting som Excel betinget formatering ikke kan gjøre. Du kan imidlertid bygge din egen VBA -kode for å gjøre dette.

Anta at du har en kolonne med data, og i den tilstøtende cellen til hvert nummer er det litt tekst som angir hvilken formatering som skal finne sted på hvert nummer.

Følgende kode vil kjøre ned listen over tall, se i cellen ved siden av for å formatere tekst, og deretter formatere nummeret etter behov:

123456789101112131415161718192021

Sub ReferToAnotherCellForConditionalFormatting ()'Lag variabler for å holde antall rader for tabelldataeneDim RRow So Long, N As Long'Fang antall rader i tabelldataområdetRRow = ActiveSheet.UsedRange.Rows.Count'Gjenta gjennom alle radene i tabelldataområdetFor N = 1 Til rad'Bruk en Select Case -setning for å evaluere formateringen basert på kolonne 2Velg Case ActiveSheet.Cells (N, 2). Verdi'Gjør den innvendige fargen til blåSak "Blå"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Gjør den indre fargen til rødSak "rød"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Gjør interiørfargen til grønnSak "Grønn"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenAvslutt VelgNeste NSlutt Sub

Når denne koden er kjørt, vil regnearket nå se slik ut:

Cellene det henvises til for formateringen, kan være hvor som helst i regnearket eller til og med på et annet regneark i arbeidsboken. Du kan bruke hvilken som helst form for tekst for å gjøre en betingelse for formateringen, og du er bare begrenset av fantasien din i bruksområdene du kan sette denne koden til.

Operatører som kan brukes i betingede formateringserklæringer

Som du har sett i de foregående eksemplene, brukes operatorer for å bestemme hvordan tilstandsverdiene vil bli evaluert f.eks. xlMellom.

Det er en rekke av disse operatørene som kan brukes, avhengig av hvordan du ønsker å spesifisere regelkriteriene.

Navn	Verdi	Beskrivelse
xlMellom	1	Mellom. Kan bare brukes hvis to formler er gitt.
xlLignende	3	Lik.
xl Større	5	Større enn.
xlGreaterEqual	7	Større enn eller lik.
xlMindre	6	Mindre enn.
xlLessEqual	8	Mindre enn eller lik.
xlNotBetween	2	Ikke mellom. Kan bare brukes hvis to formler er gitt.
xlNotEqual	4	Ikke lik.