VBA villkorlig formatering

Villkorlig formatering i Excel VBA

Vi kan använda villkorlig formatering på en cell eller ett cellområde i Excel. Ett villkorligt format är ett format som endast tillämpas på celler som uppfyller vissa kriterier, säger värden över ett visst värde, positiva eller negativa värden, eller värden med en viss formel, etc. Denna villkorliga formatering kan också göras i excel VBA-programmering med '' Formatvillkorssamlingen '' i makrot / proceduren.

Formatvillkor används för att representera ett villkorligt format som kan ställas in genom att anropa en metod som returnerar en variabel av den typen. Den innehåller alla villkorliga format för ett enda intervall och kan endast innehålla tre formatvillkor.

FormatConditions.Add / Modify / Delete används i VBA för att lägga till / modifiera / radera FormatCondition-objekt i samlingen. Varje format representeras av ett FormatCondition-objekt. FormatConditions är en egenskap för Range-objektet och Add har följande parametrar med syntax nedan:

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

Syntaxen Lägg till formel har följande argument:

  • Typ: Obligatorisk, representerar om det villkorliga formatet är baserat på det värde som finns i cellen eller ett uttryck
  • Operator: Valfritt, representerar operatören som ska användas med ett värde när 'Type' baseras på cellvärde
  • Formel1: Valfritt, representerar värdet eller uttrycket associerat med det villkorliga formatet.
  • Formel2: Valfritt, representerar värdet eller uttrycket associerat med den andra delen av villkorligt format när parametern 'Operator' antingen är 'xlBetween' eller 'xlNotBetween'

FormatConditions.Modify har också samma syntax som FormatConditions.Add.

Följande är listan över några värden / uppräkning som kan tas av vissa parametrar för 'Lägg till' / 'Ändra':

Exempel på villkorlig formatering av VBA

Nedan följer exemplen på villkorlig formatering i excel vba.

Du kan ladda ner den här VBA-villkorsformateringsmallen här - VBA-villkorsformateringsmallen

Exempel nr 1

Låt oss säga att vi har en Excel-fil som innehåller några elevers namn och betyg, och vi vill bestämma / markera markeringarna som fet och blå färg som är större än 80 och som fet och röd färg som är mindre än 50. Låt oss se informationen i filen:

Vi använder FormatConditions.Add-funktionen enligt nedan för att uppnå detta:

  • Gå till Developer -> Visual Basic Editor:

  • Högerklicka på arbetsbokens namn i rutan 'Project-VBAProject' -> 'Infoga' -> 'Modul'.

  • Skriv nu koden / proceduren i den här modulen:

Koda:

 Subformatering () Avsluta Sub 

  • Definiera variabeln rng, villkor1, villkor2:

Koda:

 Subformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition End Sub 

  • Ställ in / fixa det intervall som villkorlig formatering önskas med VBA 'Range' -funktion:

Koda:

 Subformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition Set rng = Range ("B2", "B11") End Sub 

  • Radera / rensa eventuell befintlig villkorlig formatering (om någon) från intervallet med 'FormatConditions.Radera':

Koda:

 Subformatering () Dim rng Som Range Dim condition1 Som FormatCondition, condition2 Som FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Rader End Sub Sub

  • Definiera och ställ in kriterierna för varje villkorligt format med 'FormatConditions.Add':

Koda:

 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 ") Ange villkor2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub 

  • Definiera och ställ in formatet som ska tillämpas för varje tillstånd

Kopiera och klistra in den här koden i din VBA-klassmodul.

Koda:

Subformatering () 'Definiera variablerna: Dim rng Som Range Dim condition1 Som FormatCondition, condition2 As FormatCondition' Fixing / Setting the range on which conditional formatting is to wish Ställ rng = Range ("B2", "B11") 'To radera / rensa befintlig villkorlig formatering från intervallet rng.FormatConditions.Radera 'Definiera och ställa in kriterierna för varje villkorligt format. Lägg till (xlCellValue, xlLess, "= 50") 'Definiera och ställa in formatet som ska tillämpas för varje tillstånd Med villkor1.Font.Color = vbBlue .Font.Bold = True End With With condition2 .Font.Color = vbRed .Font. Fet = True End With End Sub

Nu när vi kör den här koden med F5-tangenten eller manuellt ser vi att märkena som är mindre än 50 markeras med fetstil och rött, medan de som är större än 80 markeras med fetstil och blått enligt följande:

Obs! Några av egenskaperna för utseendet på formaterade celler som kan användas med FormatCondition är:

Exempel 2

Låt oss säga att i exemplet ovan har vi en annan kolumn som också säger att eleven är en "Topper" om han / hon får mer än 80 poäng, annars skrivs godkänd / underkänd mot dem. Nu vill vi markera de värden som anges som 'Topper' som fet och blå. Låt oss se informationen i filen:

I det här fallet fungerar koden / proceduren enligt följande:

Koda:

 Sub TextFormatting () Avsluta Sub 

Definiera och ställ in formatet som ska tillämpas för varje tillstånd

Koda:

 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 

We can see in the above code that we wish to test if the range: ‘C2:C11” contains the string: “Topper”, so the parameter: “Operator” of ‘Format.Add’ takes the enumeration:”xlContains”, to test this condition in the fixed range (i.e C2:C11), and then do the required conditional formatting (font changes) on this range.

Now when we run this code manually or by pressing the F5 key, we see that cell values with ‘Topper’ get highlighted in Blue and bold:

Note: So, we have seen in the above two examples how the ‘Add’ method works in case of any cell value criteria (numeric or text string).

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.
  • To apply more than three conditional formats to a range using the ‘Add’ method, we can use ‘If’ or ‘select case’.
  • If the ‘Add’ method has its ‘Type’ parameter as: ‘xlExpression’, then the parameter ‘Operator’ is ignored.
  • The parameters: ‘Formula1’ and ‘Formula2’ in the ‘Add’ method can be a cell reference, constant value, string value, or even a formula.
  • The parameter: ‘Formula2’ is used only when the parameter: ‘Operator’ is either ‘xlBetween’ or ‘xlNotBetween’, else it is ignored.
  • To remove all the conditional formatting from any worksheet, we can use the ‘Delete’ method as follows:
Cells.FormatConditions.Delete