Använda villkorlig formatering med Excel VBA

Villkorlig formatering i Excel

Excel Villkorlig formatering låter dig definiera regler som bestämmer cellformatering.

Du kan till exempel skapa en regel som markerar celler som uppfyller vissa kriterier. Exempel inkluderar:

• Tal som faller inom ett visst intervall (t.ex. mindre än 0).
• De 10 bästa artiklarna i en lista.
• Skapa en "värmekarta".
• "Formelbaserade" regler för praktiskt taget alla villkorliga formateringar.

I Excel finns villkorlig formatering i menyfliksområdet under Hem> Stilar (ALT> H> L).

För att skapa din egen regel, klicka på "Ny regel" och ett nytt fönster visas:

Villkorlig formatering i VBA

Alla dessa villkorliga formateringsfunktioner kan nås med VBA.

Observera att när du ställer in villkorlig formatering från VBA-kod kommer dina nya parametrar att visas i Excel-gränssnittets villkorliga formateringsfönster och vara synliga för användaren. Användaren kommer att kunna redigera eller ta bort dessa om du inte har låst kalkylbladet.

De villkorade formateringsreglerna sparas också när kalkylbladet sparas

Villkorliga formateringsregler gäller specifikt för ett visst kalkylblad och för ett visst cellintervall. Om de behövs någon annanstans i arbetsboken måste de också konfigureras på det kalkylbladet.

Praktiska användningsområden för villkorlig formatering i VBA

Du kan ha en stor del rå data importerad till ditt kalkylblad från en CSV-fil (kommaseparerade värden) eller från en databastabell eller fråga. Detta kan flyta igenom till en instrumentpanel eller rapport, med ändrade nummer importerade från en period till en annan.

Där ett nummer ändras och ligger utanför ett acceptabelt intervall, kanske du vill markera detta t.ex. cellens bakgrundsfärg i rött, och du kan göra denna inställning av villkorlig formatering. På detta sätt dras användaren direkt till detta nummer och kan sedan undersöka varför detta händer.

Du kan använda VBA för att aktivera eller inaktivera villkorlig formatering. Du kan använda VBA för att rensa reglerna för ett antal celler eller slå på dem igen. Det kan finnas en situation där det finns en helt bra anledning till ett ovanligt antal, men när användaren presenterar instrumentpanelen eller rapporterar till en högre ledningsnivå vill de kunna ta bort "larmklockorna".

På rå importerad data kanske du också vill markera var siffrorna är löjligt stora eller löjligt små. Det importerade dataintervallet är vanligtvis en annan storlek för varje period, så du kan använda VBA för att utvärdera storleken på det nya dataområdet och infoga villkorlig formatering endast för det intervallet.

Du kan också ha en situation där det finns en sorterad namnlista med numeriska värden mot var och en, t.ex. anställdas lön, provbetyg. Med villkorlig formatering kan du använda graderade färger för att gå från högsta till lägsta, vilket ser väldigt imponerande ut i presentationssyfte.

Listan med namn kommer dock inte alltid att vara statisk i storlek, och du kan använda VBA -kod för att uppdatera skalan på graderade färger enligt förändringar i intervallets storlek.

Ett enkelt exempel på att skapa ett villkorligt format på ett intervall

Detta exempel ställer in villkorlig formatering för ett cellintervall (A1: A10) i ett kalkylblad. Om talet i intervallet är mellan 100 och 150 är cellens bakgrundsfärg röd, annars har den ingen färg.

1234567891011121314

Sub ConditionalFormattingExample ()"Definiera intervallDim MyRange As RangeStäll in MyRange = Range (“A1: A10”)'Ta bort befintlig villkorlig formatering från intervalletMyRange.FormatConditions.Delete'Tillämpa villkorlig formateringMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Avsluta Sub

Lägg märke till att först definierar vi intervallet MyRange att använda villkorlig formatering.

Därefter tar vi bort befintlig villkorlig formatering för intervallet. Detta är en bra idé för att förhindra att samma regel läggs till varje gång koden körs (det är naturligtvis inte lämpligt under alla omständigheter).

Färger ges med numeriska värden. Det är en bra idé att använda RGB (röd, grön, blå) notation för detta. Du kan använda standardfärgskonstanter för detta t.ex. vbRed, vbBlue, men du är begränsad till åtta färgval.

Det finns över 16,7 miljoner färger, och med RGB kan du komma åt dem alla. Detta är mycket lättare än att försöka komma ihåg vilket nummer som passar till vilken färg. Var och en av de tre RGB -färgnumren är från 0 till 255.

Observera att parametern ‘xlBetween’ är inkluderande så cellvärden på 100 eller 150 uppfyller villkoret.

Formatering med flera villkor

Du kanske vill ställa in flera villkorliga regler inom ditt dataintervall så att alla värden i ett intervall omfattas av olika villkor:

12345678910111213141516171819

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till första regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Lägg till andra regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formel1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Lägg till tredje regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formel1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbGulAvsluta Sub

Detta exempel sätter upp den första regeln som tidigare, med cellfärgen röd om cellvärdet är mellan 100 och 150.

Ytterligare två regler läggs sedan till. Om cellvärdet är mindre än 100 är cellfärgen blå och om den är större än 150 är cellfärgen gul.

I det här exemplet måste du se till att alla talmöjligheter täcks och att reglerna inte överlappar varandra.

Om tomma celler ligger inom detta område visas de som blått, eftersom Excel fortfarande har ett värde som är mindre än 100.

Vägen runt detta är att lägga till ett annat villkor som ett uttryck. Detta måste läggas till som den första villkorsregeln i koden. Det är mycket viktigt när det finns flera regler, för att få ordning på utförandet rätt annars kan resultaten vara oförutsägbara.

1234567891011121314151617181920212223

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till första regelnMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LENG (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Lägg till andra regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Lägg till tredje regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlLess, _Formel1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Lägg till fjärde regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlGreater, _Formel1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)Avsluta Sub

Detta använder typen av xlExpression och använder sedan en standard Excel -formel för att avgöra om en cell är tom istället för ett numeriskt värde.

FormatConditions -objektet är en del av Range -objektet. Det fungerar på samma sätt som en samling med index med början från 1. Du kan iterera genom detta objekt med hjälp av en För … Nästa eller För … Varje slinga.

Radera en regel

Ibland kan du behöva ta bort en enskild regel i en uppsättning med flera regler om den inte passar datakraven.

12345678910111213

Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till första regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Radera regelnMyRange.FormatConditions (1) .DeleteAvsluta Sub

Denna kod skapar en ny regel för intervall A1: A10 och tar sedan bort den. Du måste använda rätt indexnummer för borttagningen, så kolla på 'Hantera regler' på Excel-gränssnittet (detta kommer att visa reglerna i utföringsordning) för att säkerställa att du får rätt indexnummer. Observera att det inte finns någon ångrafunktion i Excel om du tar bort en villkorlig formateringsregel i VBA, till skillnad från om du gör det via Excel-gränssnittet.

Ändra en regel

Eftersom reglerna är en samling objekt baserade på ett specifikt intervall kan du enkelt göra ändringar i vissa regler med VBA. De faktiska egenskaperna när regeln har lagts till är skrivskyddade, men du kan använda metoden Ändra för att ändra dem. Egenskaper som färger läses / skrivs.

123456789101112131415

Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till första regelnMyRange.FormatConditions.Add Type: = xlCellValue, Operator: = xlBetween, _Formel1: = "= 100", Formel2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Ändra regelMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10"'Ändra regelfärgMyRange.FormatConditions (1) .Interior.Color = vbGreenAvsluta Sub

Denna kod skapar ett intervallobjekt (A1: A10) och lägger till en regel för tal mellan 100 och 150. Om villkoret är sant ändras cellfärgen till rött.

Koden ändrar sedan regeln till siffror mindre än 10. Om villkoret är sant ändras cellfärgen nu till grönt.

Använda ett graderat färgschema

Villkorlig formatering i Excel har ett sätt att använda graderade färger på ett antal nummer som körs i stigande eller fallande ordning.

Detta är mycket användbart om du har data som försäljningssiffror efter geografiskt område, stadstemperaturer eller avstånd mellan städer. Med VBA har du den extra fördelen att du kan välja ditt eget färgsättningsschema, snarare än de vanliga som erbjuds på Excel-gränssnittet.

1234567891011121314151617181920212223242526272829

Sub GraduatedColors ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Definiera skalatypMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Välj färg för det lägsta värdet i intervalletMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueMed MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Color = 7039480Sluta med'Välj färg för de mellersta värdena i intervalletMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Välj färg för mittpunkten i intervalletMed MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Färg = 8711167Sluta med'Välj färg för det högsta värdet i intervalletMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueMed MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Color = 8109667Sluta medAvsluta Sub

När denna kod körs kommer den att gradera cellfärgerna enligt de stigande värdena i intervallet A1: A10.

Detta är ett mycket imponerande sätt att visa data och kommer säkert att fånga användarnas uppmärksamhet.

Villkorlig formatering för felvärden

När du har en enorm mängd data kan du enkelt missa ett felvärde i dina olika kalkylblad. Om detta presenteras för en användare utan att lösas kan det leda till stora problem och användaren tappar förtroendet för siffrorna. Detta använder en regel typ av xlExpression och en Excel -funktion av IsError för att utvärdera cellen.

Du kan skapa kod så att alla celler med fel har en röd cellfärg:

1234567891011

Sub ErrorConditionalFormattingExample ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till felregelMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Ställ in interiörfärgen till rödMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Avsluta Sub

Villkorlig formatering för tidigare datum

Du kan ha data importerade där du vill markera datum som är tidigare. Ett exempel på detta kan vara en gäldenärsrapport där du vill att gamla fakturadatum som är över 30 dagar gamla ska sticka ut.

Denna kod använder regeltypen xlExpression och en Excel -funktion för att utvärdera datumen.

1234567891011

Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Skapa intervallobjekt baserat på en datumkolumnStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till felregel för tidigare datumMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now ()-A1> 30"'Ställ in interiörfärgen till rödMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Avsluta Sub

Denna kod kommer att ta ett datumintervall i intervallet A1: A10 och ställer in cellfärgen till röd för alla datum som är över 30 dagar tidigare.

I formeln som används i villkoret ger Now () aktuellt datum och tid. Detta kommer att fortsätta beräkna varje gång kalkylbladet beräknas om, så formateringen ändras från en dag till en annan.

Använda datafält i VBA villkorlig formatering

Du kan använda VBA för att lägga till datafält i ett antal nummer. Dessa är nästan som mini -diagram och ger en omedelbar bild av hur stora siffrorna är i förhållande till varandra. Genom att acceptera standardvärden för dataraderna är koden mycket lätt att skriva.

123456

Sub DataBarFormattingExample ()Dim MyRange As RangeStäll in MyRange = Range (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarAvsluta Sub

Dina data kommer att se ut så här i kalkylbladet:

Använda ikoner i VBA villkorlig formatering

Du kan använda villkorlig formatering för att placera ikoner bredvid dina nummer i ett kalkylblad. Ikonerna kan vara pilar eller cirklar eller olika andra former. I det här exemplet lägger koden till pilikoner till siffrorna baserat på deras procentuella värden:

12345678910111213141516171819202122232425

Sub IconSetsExample ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till ikonuppsättning i FormatConditions -objektetMyRange.FormatConditions.AddIconSetCondition'Ställ in ikonen på pilar - villkor 1Med MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Sluta med'ställ in ikonkriterierna för det procentuella värdet som krävs - villkor 2Med MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Värde = 33.Operator = xlGreaterEqualSluta med'ställ in ikonkriterierna för det procentuella värdet som krävs - villkor 3Med MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Värde = 67.Operator = xlGreaterEqualSluta medAvsluta Sub

Detta ger en omedelbar vy som visar om ett tal är högt eller lågt. När du har kört den här koden kommer ditt kalkylblad att se ut så här:

Använda villkorlig formatering för att markera de fem bästa

Du kan använda VBA -kod för att markera de fem bästa siffrorna inom ett dataområde. Du använder en parameter som heter ‘AddTop10’, men du kan justera rangnumret i koden till 5. En användare kanske vill se de högsta siffrorna i ett område utan att behöva sortera data först.

1234567891011121314151617181920212223

Sub Top5Example ()Dim MyRange As Range'Skapa intervallobjektStäll in MyRange = Range (“A1: A10”)'Radera tidigare villkorade formatMyRange.FormatConditions.Delete'Lägg till ett Top10 -villkorMyRange.FormatConditions.AddTop10Med MyRange.FormatConditions (1)'Ställ in parameter från topp till botten.TopBottom = xlTop10Top'Endast topp 5.Rank = 5Sluta medMed MyRange.FormatConditions (1) .Font'Ange teckensnittsfärg.Color = -16383844Sluta medMed MyRange.FormatConditions (1) .Interior'Ställ in cellens bakgrundsfärg.Color = 13551615Sluta medAvsluta Sub

Data i ditt kalkylblad ser ut så här efter att koden har körts:

Observera att värdet 145 visas två gånger så att sex celler markeras.

Betydelsen av parametrarna StopIfTrue och SetFirstPriority

StopIfTrue är av betydelse om ett cellintervall har flera villkorliga formateringsregler. En enda cell inom intervallet kan uppfylla den första regeln, men den kan också uppfylla efterföljande regler. Som utvecklare kanske du vill att den bara ska visa formateringen för den första regeln som den gäller. Andra regelkriterier kan överlappa varandra och kan göra oavsiktliga ändringar om de får fortsätta nedåt i listan.

Standardinställningen för denna parameter är True men du kan ändra den om du vill att alla andra regler för den cellen ska beaktas:

1	MyRange. FormatConditions (1) .StopIfTrue = False

Parametern SetFirstPriority dikterar om villkorsregeln kommer att utvärderas först när det finns flera regler för den cellen.

1	MyRange. FormatConditions (1) .SetFirstPriority

Detta flyttar positionen för den regeln till position 1 inom samlingen av formatvillkor, och andra regler flyttas nedåt med ändrade indexnummer. Var försiktig om du gör några ändringar i reglerna i koden med hjälp av indexnumren. Du måste se till att du ändrar eller tar bort rätt regel.

Du kan ändra prioriteten för en regel:

1	MyRange. FormatConditions (1) .Priority = 3

Detta kommer att ändra de relativa positionerna för alla andra regler inom listan med villkorligt format.

Använda villkorlig formatering med hänvisning till andra cellvärden

Detta är en sak som Excel villkorlig formatering inte kan göra. Du kan dock bygga din egen VBA -kod för att göra detta.

Anta att du har en kolumn med data, och i den intilliggande cellen till varje nummer finns en text som anger vilken formatering som ska ske på varje nummer.

Följande kod kör ner din lista med siffror, leta i den intilliggande cellen för att formatera text och formatera sedan numret efter behov:

123456789101112131415161718192021

Sub ReferToAnotherCellForConditionalFormatting ()'Skapa variabler för att hålla antalet rader för tabelldataDim RRow As Long, N As Long'Fånga antalet rader inom tabelldataintervalletRRow = ActiveSheet.UsedRange.Rows.Count'Iterera genom alla rader i tabelldataområdetFör N = 1 Till rad'Använd en Select Case -sats för att utvärdera formateringen baserat på kolumn 2Välj Case ActiveSheet.Cells (N, 2) .Värde'Vänd den inre färgen till blåSkalet "Blått"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Vänd den inre färgen till rödSkalet "rött"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Vänd den inre färgen till grönSkalet "grönt"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenAvsluta VäljNästa NAvsluta Sub

När den här koden har körts kommer ditt kalkylblad nu att se ut så här:

Cellerna som det hänvisas till för formateringen kan vara var som helst i kalkylbladet eller till och med på ett annat kalkylblad i arbetsboken. Du kan använda vilken text som helst för att skapa ett villkor för formateringen, och du är bara begränsad av din fantasi i de användningsområden som du kan sätta denna kod till.

Operatörer som kan användas i Villkorlig formatering

Som du har sett i de tidigare exemplen används operatorer för att bestämma hur villkorsvärdena kommer att utvärderas t.ex. xlBetween.

Det finns ett antal av dessa operatörer som kan användas, beroende på hur du vill ange dina regelkriterier.

namn	Värde	Beskrivning
xlBetween	1	Mellan. Kan endast användas om två formler tillhandahålls.
xlKvart	3	Likvärdig.
xl Större	5	Större än.
xlGreaterEqual	7	Större än eller lika med.
xlMindre	6	Mindre än.
xlLessEqual	8	Mindre än eller lika med.
xlNotBetween	2	Inte mellan. Kan endast användas om två formler tillhandahålls.
xlNotEqual	4	Inte jämnlikt.