Formattazione condizionale Excel
La formattazione condizionale di Excel consente di definire regole che determinano la formattazione delle celle.
Ad esempio, puoi creare una regola che evidenzi le celle che soddisfano determinati criteri. Esempi inclusi:
- Numeri che rientrano in un certo intervallo (es. Meno di 0).
- I primi 10 elementi in un elenco.
- Creazione di una "mappa di calore".
- Regole "basate su formule" per praticamente qualsiasi formattazione condizionale.
In Excel, la formattazione condizionale si trova nella barra multifunzione in Home > Stili (ALT > H > L).
Per creare la tua regola, fai clic su "Nuova regola" e apparirà una nuova finestra:
Formattazione condizionale in VBA
È possibile accedere a tutte queste funzionalità di formattazione condizionale utilizzando VBA.
Tieni presente che quando imposti la formattazione condizionale dal codice VBA, i nuovi parametri verranno visualizzati nella finestra di formattazione condizionale front-end di Excel e saranno visibili all'utente. L'utente sarà in grado di modificarli o eliminarli a meno che tu non abbia bloccato il foglio di lavoro.
Le regole di formattazione condizionale vengono salvate anche quando viene salvato il foglio di lavoro
Le regole di formattazione condizionale si applicano specificamente a un particolare foglio di lavoro ea un particolare intervallo di celle. Se sono necessari altrove nella cartella di lavoro, devono essere impostati anche su quel foglio di lavoro.
Usi pratici della formattazione condizionale in VBA
Potresti avere una grossa fetta di dati grezzi importati nel tuo foglio di lavoro da un file CSV (valori separati da virgola) o da una tabella o query di database. Questo può fluire in un dashboard o in un report, con numeri che cambiano importati da un periodo all'altro.
Se un numero cambia ed è al di fuori di un intervallo accettabile, potresti volerlo evidenziare, ad es. colore di sfondo della cella in rosso e puoi farlo impostando la formattazione condizionale. In questo modo, l'utente sarà immediatamente attratto da questo numero e potrà quindi indagare sul motivo per cui ciò sta accadendo.
Puoi utilizzare VBA per attivare o disattivare la formattazione condizionale. Puoi utilizzare VBA per cancellare le regole su un intervallo di celle o riattivarle. Potrebbe esserci una situazione in cui c'è una ragione perfettamente valida per un numero insolito, ma quando l'utente presenta la dashboard o segnala a un livello superiore di gestione, vuole essere in grado di rimuovere i "campanelli d'allarme".
Inoltre, sui dati grezzi importati, potresti voler evidenziare dove i numeri sono ridicolmente grandi o ridicolmente piccoli. L'intervallo di dati importato ha in genere una dimensione diversa per ogni periodo, quindi puoi utilizzare VBA per valutare la dimensione del nuovo intervallo di dati e inserire la formattazione condizionale solo per quell'intervallo.
Potresti anche avere una situazione in cui c'è un elenco ordinato di nomi con valori numerici contro ciascuno, ad es. stipendio dipendente, voti esami. Con la formattazione condizionale, puoi utilizzare i colori graduati per passare dal più alto al più basso, il che sembra molto impressionante ai fini della presentazione.
Tuttavia, l'elenco dei nomi non sarà sempre di dimensioni statiche ed è possibile utilizzare il codice VBA per aggiornare la scala dei colori graduati in base alle modifiche nelle dimensioni dell'intervallo.
Un semplice esempio di creazione di un formato condizionale su un intervallo
Questo esempio imposta la formattazione condizionale per un intervallo di celle (A1:A10) in un foglio di lavoro. Se il numero nell'intervallo è compreso tra 100 e 150, il colore di sfondo della cella sarà rosso, altrimenti non avrà alcun colore.
| 1234567891011121314 | Esempio di formattazione subcondizionale()"Definisci intervallo"Dim MyRange As RangeImposta MyRange = Range ("A1: A10")"Elimina formattazione condizionale esistente dall'intervallo"MyRange.FormatConditions.Delete"Applica formattazione condizionale"MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)Fine sottotitolo |
Nota che prima definiamo l'intervallo La mia gamma per applicare la formattazione condizionale.
Quindi eliminiamo qualsiasi formattazione condizionale esistente per l'intervallo. Questa è una buona idea per evitare che la stessa regola venga aggiunta ogni volta che viene eseguito il codice (ovviamente non sarà appropriato in tutte le circostanze).
I colori sono dati da valori numerici. È una buona idea usare la notazione RGB (Rosso, Verde, Blu) per questo. È possibile utilizzare costanti di colore standard per questo, ad es. vbRed, vbBlue, ma sei limitato a otto scelte di colore.
Sono disponibili oltre 16,7 milioni di colori e utilizzando RGB puoi accedervi tutti. Questo è molto più facile che cercare di ricordare quale numero va con quale colore. Ciascuno dei tre numeri di colore RGB va da 0 a 255.
Si noti che il parametro "xlBetween" è inclusivo, quindi i valori delle celle di 100 o 150 soddisferanno la condizione.
Formattazione multi-condizionale
Potresti voler impostare diverse regole condizionali all'interno del tuo intervallo di dati in modo che tutti i valori in un intervallo siano coperti da condizioni diverse:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete'Aggiungi la prima regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)'Aggiungi seconda regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _Formula1:="=100"MyRange.FormatConditions(2).Interior.Color = vbBlue'Aggiungi terza regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _Formula1:="=150"MyRange.FormatConditions(3).Interior.Color = vbYellowFine sottotitolo |
Questo esempio imposta la prima regola come prima, con il colore della cella rosso se il valore della cella è compreso tra 100 e 150.
Vengono quindi aggiunte altre due regole. Se il valore della cella è inferiore a 100, il colore della cella è blu e se è maggiore di 150, il colore della cella è giallo.
In questo esempio, devi assicurarti che tutte le possibilità di numeri siano coperte e che le regole non si sovrappongano.
Se le celle vuote si trovano in questo intervallo, verranno visualizzate in blu, perché Excel le considera ancora con un valore inferiore a 100.
Il modo per aggirare questo è aggiungere un'altra condizione come espressione. Questo deve essere aggiunto come prima regola di condizione all'interno del codice. È molto importante quando ci sono più regole, per ottenere l'ordine di esecuzione corretto, altrimenti i risultati potrebbero essere imprevedibili.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete'Aggiungi la prima regolaMyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _"= LUNGHEZZA(TRIM(A1))=0"MyRange.FormatConditions(1).Interior.Pattern = xlNessuno'Aggiungi seconda regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(2).Interior.Color = RGB(255, 0, 0)'Aggiungi terza regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _Formula1:="=100"MyRange.FormatConditions(3).Interior.Color = vbBlue'Aggiungi la quarta regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _Formula1:="=150"MyRange.FormatConditions(4).Interior.Color = RGB(0, 255, 0)Fine sottotitolo |
Utilizza il tipo di xlExpression e quindi utilizza una formula standard di Excel per determinare se una cella è vuota anziché un valore numerico.
L'oggetto FormatConditions fa parte dell'oggetto Range. Agisce allo stesso modo di una raccolta con l'indice che inizia da 1. È possibile scorrere questo oggetto utilizzando un ciclo For… Next o For… Each.
Eliminazione di una regola
A volte, potrebbe essere necessario eliminare una singola regola in un insieme di più regole se non soddisfa i requisiti dei dati.
| 12345678910111213 | Sub DeleteConditionalFormattingExample()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete'Aggiungi la prima regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)'Elimina regolaMyRange.FormatConditions(1).EliminaFine sottotitolo |
Questo codice crea una nuova regola per l'intervallo A1: A10, quindi la elimina. Devi utilizzare il numero di indice corretto per l'eliminazione, quindi controlla "Gestisci regole" sul front-end di Excel (questo mostrerà le regole in ordine di esecuzione) per assicurarti di ottenere il numero di indice corretto. Nota che non c'è nessuna funzione di annullamento in Excel se elimini una regola di formattazione condizionale in VBA, a differenza di se lo fai tramite il front-end di Excel.
Modifica di una regola
Poiché le regole sono una raccolta di oggetti basata su un intervallo specificato, puoi facilmente apportare modifiche a regole particolari utilizzando VBA. Le proprietà effettive una volta aggiunta la regola sono di sola lettura, ma è possibile utilizzare il metodo Modify per modificarle. Le proprietà come i colori vengono lette/scritte.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete'Aggiungi la prima regolaMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formula1:="=100", Formula2:="=150"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)'Cambia regolaMyRange.FormatConditions(1).Modifica xlCellValue, xlLess, "10""Cambia il colore della regola"MyRange.FormatConditions(1).Interior.Color = vbGreenFine sottotitolo |
Questo codice crea un oggetto intervallo (A1:A10) e aggiunge una regola per i numeri compresi tra 100 e 150. Se la condizione è vera, il colore della cella diventa rosso.
Il codice quindi modifica la regola in numeri inferiori a 10. Se la condizione è vera, il colore della cella ora diventa verde.
Utilizzo di una combinazione di colori graduata
La formattazione condizionale di Excel consente di utilizzare colori graduati su un intervallo di numeri in ordine crescente o decrescente.
Questo è molto utile dove hai dati come i dati di vendita per area geografica, temperature della città o distanze tra le città. Usando VBA, hai l'ulteriore vantaggio di poter scegliere la tua combinazione di colori graduata, piuttosto che quelli standard offerti sul front-end di Excel.
| 1234567891011121314151617181920212223242526272829 | SubgradedColors()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete"Definisci il tipo di scala"MyRange.FormatConditions.AddColorScale ColorScaleType:=3'Seleziona il colore per il valore più basso nell'intervalloMyRange.FormatConditions(1).ColorScaleCriteria(1).Type = _xlConditionValueLowestValueCon MyRange.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Colore = 7039480Termina con'Seleziona il colore per i valori intermedi nell'intervalloMyRange.FormatConditions(1).ColorScaleCriteria(2).Type = _xlConditionValuePercentileMyRange.FormatConditions(1).ColorScaleCriteria(2).Value = 50'Seleziona il colore per il punto medio dell'intervalloCon MyRange.FormatConditions(1).ColorScaleCriteria(2).FormatColor.Colore = 8711167Termina con'Seleziona il colore per il valore più alto nell'intervalloMyRange.FormatConditions(1).ColorScaleCriteria(3).Type = _xlConditionValueHighestValueCon MyRange.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Colore = 8109667Termina conFine sottotitolo |
Quando questo codice viene eseguito, graduerà i colori delle celle in base ai valori crescenti nell'intervallo A1: A10.
Questo è un modo molto impressionante di visualizzare i dati e attirerà sicuramente l'attenzione degli utenti.
Formattazione condizionale per valori di errore
Quando hai un'enorme quantità di dati, potresti facilmente perdere un valore di errore nei tuoi vari fogli di lavoro. Se questo viene presentato a un utente senza essere risolto, potrebbe portare a grossi problemi e l'utente perde fiducia nei numeri. Questo usa un tipo di regola di xlExpression e una funzione di Excel di IsError per valutare la cella.
Puoi creare codice in modo che tutte le celle con errori abbiano un colore di cella rosso:
| 1234567891011 | Sub ErrorConditionalFormattingExample()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete'Aggiungi regola di erroreMyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=IsError(A1)=true""Imposta il colore degli interni su rosso"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)Fine sottotitolo |
Formattazione condizionale per date nel passato
Potresti avere dati importati in cui desideri evidenziare le date che sono nel passato. Un esempio di ciò potrebbe essere un rapporto sui debitori in cui si desidera che le vecchie date di fattura di oltre 30 giorni si distinguano.
Questo codice usa il tipo di regola xlExpression e una funzione di Excel per valutare le date.
| 1234567891011 | Sub DateInPastConditionalFormattingExample()Dim MyRange As Range'Crea oggetto intervallo in base a una colonna di dateImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete'Aggiungi regola di errore per le date nel passatoMyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=Now()-A1 > 30""Imposta il colore degli interni su rosso"MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)Fine sottotitolo |
Questo codice prenderà un intervallo di date nell'intervallo A1: A10 e imposterà il colore della cella su rosso per qualsiasi data che è più di 30 giorni nel passato.
Nella formula utilizzata nella condizione, Now() fornisce la data e l'ora correnti. Questo continuerà a ricalcolare ogni volta che il foglio di lavoro viene ricalcolato, quindi la formattazione cambierà da un giorno all'altro.
Utilizzo delle barre dei dati nella formattazione condizionale VBA
Puoi utilizzare VBA per aggiungere barre dati a un intervallo di numeri. Questi sono quasi come mini grafici e danno una visione istantanea di quanto grandi siano i numeri in relazione l'uno con l'altro. Accettando i valori predefiniti per le barre dei dati, il codice è molto facile da scrivere.
| 123456 | Sub DataBarFormattingExample()Dim MyRange As RangeImposta MyRange = Range ("A1: A10")MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarFine sottotitolo |
I tuoi dati appariranno così sul foglio di lavoro:
Utilizzo delle icone nella formattazione condizionale VBA
Puoi utilizzare la formattazione condizionale per mettere le icone accanto ai tuoi numeri in un foglio di lavoro. Le icone possono essere frecce o cerchi o varie altre forme. In questo esempio, il codice aggiunge icone freccia ai numeri in base ai loro valori percentuali:
| 12345678910111213141516171819202122232425 | Sub IconSetsEsempio()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete'Aggiungi il set di icone all'oggetto FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Imposta l'icona su frecce - condizione 1Con MyRange.FormatConditions(1).IconSet = ActiveWorkbook.IconSets(xl3Arrows)Termina con'imposta i criteri dell'icona per il valore percentuale richiesto - condizione 2Con MyRange.FormatConditions(1).IconCriteria(2).Type = xlConditionValuePercent.Valore = 33.Operatore = xlGreaterEqualTermina con'imposta i criteri dell'icona per il valore percentuale richiesto - condizione 3Con MyRange.FormatConditions(1).IconCriteria(3).Type = xlConditionValuePercent.Valore = 67.Operatore = xlGreaterEqualTermina conFine sottotitolo |
Questo darà una vista istantanea che mostra se un numero è alto o basso. Dopo aver eseguito questo codice, il tuo foglio di lavoro sarà simile a questo:
Utilizzo della formattazione condizionale per evidenziare i primi cinque
Puoi utilizzare il codice VBA per evidenziare i primi 5 numeri all'interno di un intervallo di dati. Utilizzi un parametro chiamato "AddTop10", ma puoi regolare il numero di rango all'interno del codice su 5. Un utente potrebbe voler vedere i numeri più alti in un intervallo senza dover prima ordinare i dati.
| 1234567891011121314151617181920212223 | Sub Top5Esempio()Dim MyRange As Range'Crea oggetto intervalloImposta MyRange = Range ("A1: A10")'Elimina i formati condizionali precedentiMyRange.FormatConditions.Delete"Aggiungi una condizione Top10"MyRange.FormatConditions.AddTop10Con MyRange.FormatConditions(1)'Imposta parametro dall'alto in basso.TopBottom = xlTop10Top"Solo tra i primi 5".Rango = 5Termina conCon MyRange.FormatConditions(1).Font'Imposta il colore del carattere.Colore = -16383844Termina conCon MyRange.FormatConditions(1).Interior'Imposta il colore di sfondo della cella.Colore = 13551615Termina conFine sottotitolo |
I dati sul tuo foglio di lavoro apparirebbero così dopo aver eseguito il codice:
Si noti che il valore di 145 viene visualizzato due volte, quindi vengono evidenziate sei celle.
Significato dei parametri StopIfTrue e SetFirstPriority
StopIfTrue è importante se un intervallo di celle ha più regole di formattazione condizionale. Una singola cella all'interno dell'intervallo può soddisfare la prima regola, ma può anche soddisfare le regole successive. Come sviluppatore, potresti voler visualizzare la formattazione solo per la prima regola a cui si riferisce. Altri criteri di regole possono sovrapporsi e possono apportare modifiche non intenzionali se autorizzati a continuare l'elenco delle regole.
L'impostazione predefinita su questo parametro è True ma è possibile modificarlo se si desidera considerare tutte le altre regole per quella cella:
| 1 | La mia gamma. FormatConditions(1).StopIfTrue = False |
Il parametro SetFirstPriority determina se quella regola di condizione verrà valutata per prima quando sono presenti più regole per quella cella.
| 1 | La mia gamma. FormatConditions(1).SetFirstPriority |
Ciò sposta la posizione di quella regola alla posizione 1 all'interno della raccolta di condizioni di formato e qualsiasi altra regola verrà spostata verso il basso con i numeri di indice modificati. Fare attenzione se si apportano modifiche alle regole nel codice utilizzando i numeri di indice. È necessario assicurarsi di modificare o eliminare la regola corretta.
Puoi modificare la priorità di una regola:
| 1 | La mia gamma. FormatConditions(1).Priority=3 |
Ciò cambierà le posizioni relative di qualsiasi altra regola all'interno dell'elenco dei formati condizionali.
Utilizzo della formattazione condizionale che fa riferimento ad altri valori di cella
Questa è una cosa che la formattazione condizionale di Excel non può fare. Tuttavia, puoi creare il tuo codice VBA per farlo.
Supponiamo di avere una colonna di dati e, nella cella adiacente a ciascun numero, ci sia del testo che indica quale formattazione dovrebbe avvenire su ciascun numero.
Il seguente codice eseguirà l'elenco dei numeri, cercherà nella cella adiacente la formattazione del testo, quindi formatterà il numero come richiesto:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting()'Crea variabili per contenere il numero di righe per i dati tabulariDim Rrow quanto a lungo, N quanto a lungo'Cattura il numero di righe all'interno dell'intervallo di dati tabulariRRow = ActiveSheet.UsedRange.Rows.Count'Esegui itera su tutte le righe nell'intervallo di dati tabulariPer N = 1 a RRow'Utilizzare un'istruzione Select Case per valutare la formattazione in base alla colonna 2Seleziona Case ActiveSheet.Cells(N, 2).Value'Trasforma il colore degli interni in bluCustodia "Blu"ActiveSheet.Cells(N, 1).Interior.Color = vbBlue"Imposta il colore degli interni in rosso"Caso "rosso"ActiveSheet.Cells(N, 1).Interior.Color = vbRed'Trasforma il colore degli interni in verdeCustodia "Verde"ActiveSheet.Cells(N, 1).Interior.Color = vbGreenFine selezioneSuccessivo NFine sottotitolo |
Una volta che questo codice è stato eseguito, il tuo foglio di lavoro sarà ora simile a questo:
Le celle a cui si fa riferimento per la formattazione potrebbero trovarsi ovunque nel foglio di lavoro o anche in un altro foglio di lavoro all'interno della cartella di lavoro. Puoi usare qualsiasi forma di testo per creare una condizione per la formattazione e sei limitato solo dalla tua immaginazione negli usi a cui potresti mettere questo codice.
Operatori che possono essere utilizzati nelle istruzioni di formattazione condizionale
Come hai visto negli esempi precedenti, gli operatori vengono utilizzati per determinare come verranno valutati i valori delle condizioni, ad es. xlTra.
È possibile utilizzare un certo numero di questi operatori, a seconda di come si desidera specificare i criteri della regola.
| Nome | Valore | Descrizione |
| xlTra | 1 | Tra. Può essere utilizzato solo se vengono fornite due formule. |
| xlEqual | 3 | Pari. |
| xlmaggiore | 5 | Più grande di. |
| xlMaggioreuguale | 7 | Maggiore o uguale a. |
| xlMeno | 6 | Meno di. |
| xlMenouguale | 8 | Minore o uguale a. |
| xlNotBetween | 2 | Non in mezzo. Può essere utilizzato solo se vengono fornite due formule. |
| xlNotEqual | 4 | Non uguale. |
