Formattazione condizionale VBA

Formattazione condizionale in Excel VBA

Possiamo applicare la formattazione condizionale a una cella o a un intervallo di celle in Excel. Un formato condizionale è un formato che viene applicato solo a celle che soddisfano determinati criteri, ad esempio valori sopra un valore particolare, valori positivi o negativi o valori con una formula particolare, ecc. Questa formattazione condizionale può essere eseguita anche nella programmazione VBA di Excel utilizzando la " Raccolta delle condizioni di formato " nella macro / procedura.

La condizione di formato viene utilizzata per rappresentare un formato condizionale che può essere impostato chiamando un metodo che restituisce una variabile di quel tipo. Contiene tutti i formati condizionali per un singolo intervallo e può contenere solo tre condizioni di formato.

FormatConditions.Add / Modify / Delete viene utilizzato in VBA per aggiungere / modificare / eliminare oggetti FormatCondition alla raccolta. Ogni formato è rappresentato da un oggetto FormatCondition. FormatConditions è una proprietà dell'oggetto Range e Add ha i seguenti parametri con la sintassi seguente:

FormatConditions.Add (Tipo, Operatore, Formula1, Formula2)

Gli argomenti della sintassi della formula Aggiungi sono i seguenti:

  • Tipo: obbligatorio, rappresenta se il formato condizionale è basato sul valore presente nella cella o in un'espressione
  • Operatore: facoltativo, rappresenta l'operatore da utilizzare con un valore quando "Tipo" è basato sul valore della cella
  • Formula1: facoltativo, rappresenta il valore o l'espressione associata al formato condizionale.
  • Formula2: facoltativa, rappresenta il valore o l'espressione associata alla seconda parte del formato condizionale quando il parametro: "Operator" è "xlBetween" o "xlNotBetween"

FormatConditions.Modify ha anche la stessa sintassi di FormatConditions.Add.

Di seguito è riportato l'elenco di alcuni valori / enumerazioni che possono essere presi da alcuni parametri di "Aggiungi" / "Modifica":

Esempi di formattazione condizionale VBA

Di seguito sono riportati gli esempi di formattazione condizionale in excel vba.

Puoi scaricare questo modello di formattazione condizionale VBA qui - Modello di formattazione condizionale VBA

Esempio 1

Supponiamo di avere un file Excel contenente il nome e i voti di alcuni studenti e desideriamo determinare / evidenziare i voti come grassetto e di colore blu che è maggiore di 80, e come grassetto e rosso a colori che è inferiore a 50. Vediamo i dati contenuti nel file:

Usiamo la funzione FormatConditions.Add come di seguito per ottenere ciò:

  • Vai a Sviluppatore -> Visual Basic Editor:

  • Fare clic con il pulsante destro del mouse sul nome della cartella di lavoro nel riquadro "Progetto-VBAProject "->" Inserisci "->" Modulo ".

  • Ora scrivi il codice / procedura in questo modulo:

Codice:

 Sub formatting () End Sub

  • Definisci la variabile rng, condition1, condition2:

Codice:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition End Sub

  • Imposta / correggi l'intervallo su cui si desidera la formattazione condizionale utilizzando la funzione 'Intervallo' VBA:

Codice:

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

  • Elimina / cancella qualsiasi formattazione condizionale esistente (se presente) dall'intervallo, utilizzando "FormatConditions.Delete":

Codice:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub

  • Ora definisci e imposta i criteri per ogni formato condizionale, utilizzando 'FormatConditions.Add':

Codice:

 Sub formatting () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Imposta condizione1 = rng.FormatConditions.Add (xlCellValue, xlGorgeous, "= 80 ") Imposta condizione2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub

  • Definisci e imposta il formato da applicare per ciascuna condizione

Copia e incolla questo codice nel tuo modulo di classe VBA.

Codice:

Sub formatting () 'Definizione delle variabili: Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition' Correzione / impostazione dell'intervallo su cui si desidera la formattazione condizionale Set rng = Range ("B2", "B11") ' elimina / cancella qualsiasi formattazione condizionale esistente dall'intervallo rng.FormatConditions.Delete 'Definizione e impostazione dei criteri per ogni formato condizionale Set condition1 = rng.FormatConditions.Add (xlCellValue, xlGuesday, "= 80") Set condition2 = rng.FormatConditions. Aggiungi (xlCellValue, xlLess, "= 50") 'Definizione e impostazione del formato da applicare per ogni condizione With condition1 .Font.Color = vbBlue .Font.Bold = True End With With condition2 .Font.Color = vbRed .Font. Grassetto = True End With End Sub

Ora, quando eseguiamo questo codice utilizzando il tasto F5 o manualmente, vediamo che i segni inferiori a 50 vengono evidenziati in grassetto e rosso, mentre quelli maggiori di 80 vengono evidenziati in grassetto e blu come segue:

Nota: alcune delle proprietà per l'aspetto delle celle formattate che possono essere utilizzate con FormatCondition sono:

Esempio n. 2

Diciamo che nell'esempio precedente abbiamo anche un'altra colonna che afferma che lo studente è un "Topper" se ottiene più di 80 punti, altrimenti viene scritto Pass / Fail. Ora desideriamo evidenziare i valori indicati come "Topper" in grassetto e blu. Vediamo i dati contenuti nel file:

In questo caso, il codice / procedura funzionerebbe come segue:

Codice:

 Sub TextFormatting () End Sub

Definisci e imposta il formato da applicare per ciascuna condizione

Codice:

 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

Possiamo vedere nel codice sopra che vogliamo testare se l'intervallo: 'C2: C11 "contiene la stringa:" Topper ", quindi il parametro:" Operator "di" Format.Add "prende l'enumerazione:" xlContains ", per testare questa condizione nell'intervallo fisso (ad esempio C2: C11), quindi eseguire la formattazione condizionale richiesta (modifiche dei caratteri) su questo intervallo.

Ora, quando eseguiamo questo codice manualmente o premendo il tasto F5, vediamo che i valori delle celle con "Topper" vengono evidenziati in blu e in grassetto:

Nota: quindi, abbiamo visto nei due esempi precedenti come funziona il metodo "Aggiungi" in caso di criteri di valore di cella (stringa numerica o di testo).

Di seguito sono riportate alcune altre istanze / criteri che possono essere utilizzati per testare e quindi applicare la formattazione condizionale VBA su:

  • Formato per periodo di tempo
  • Condizione media
  • Condizione della scala dei colori
  • IconSet condizione
  • Stato della Databar
  • Valori unici
  • Valori duplicati
  • Valori Top10
  • Condizione percentile
  • Condizione degli spazi vuoti, ecc.

Con diverse condizioni da testare, diversi valori / enumerazioni vengono presi dai parametri di "Aggiungi".

Cose da ricordare sulla formattazione condizionale VBA

  • Il metodo "Aggiungi" con "FormatConditions" viene utilizzato per creare un nuovo formato condizionale, il metodo "Elimina" per eliminare qualsiasi formato condizionale e il metodo "Modifica" per modificare qualsiasi formato condizionale esistente.
  • Il metodo "Aggiungi" con "Raccolta FormatConditions" non riesce se vengono creati più di tre formati condizionali per un singolo intervallo.
  • 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