I. Introduction▲
La mise en forme conditionnelle applique un format (ombrage de cellule,
couleur de police ou de cadre) automatiquement si une condition particulière est remplie.
La MFC permet ainsi de mettre en évidence et de visualiser rapidement certaines données dans
un tableau de résultats.
II. Description▲
Les mises en forme conditionnelles sont accessibles par le menu Format/Mise en forme conditionnelle.
La boîte de dialogue vous permet de paramétrer les conditions en fonction de votre projet.
La mise en forme sera appliquée à la cellule (ou plage de cellules) active.
Vous pouvez utiliser deux types de conditions:
* En fonction du contenu de la cellule: Sélectionnez l'option
"La valeur de la cellule est".
* En définissant une formule: Sélectionnez l'option "La formule est".
Une liste d'opérateurs est disponible lorsque vous choisissez
l'option "La valeur de la cellule est".
Sélectionnez un opérateur dans la liste puis indiquez les données à contrôler dans
le(s) champ(s) de droite:
Vous pouvez faire référence à des données alpha numériques ou au contenu d'autres cellules.
Dans l'exemple suivant, la condition est vraie si le contenu de la cellule qui reçoit la
mise en forme est compris entre 15 et 20.
Dans cet autre exemple, la condition est vraie si la valeur de la cellule est
comprise entre le contenu des cellules B1 et B2.
Le type de condition "La formule est" teste une fonction logique.
L'expression utilisée dans vos mises en forme conditionnelles doit donc renvoyer un
résultat Vrai ou Faux.
L'application Excel dispose de fonctions logiques spécifiques:
* ET
* FAUX
* NON
* OU
* VRAI
* EST.IMPAIR
* EST.PAIR
* ESTERREUR
* ESTLOGIQUE
* ESTNA
* ESTNONTEXTE
* ESTNUM
* ESTREF
* ESTTEXTE
* ESTVIDE
* EXACT
Mais vous pouvez aussi créer vos propres fonctions logiques.
Par exemple: =$A$1="mimi"
Cette formule renvoie la valeur VRAI si la cellule A1 contient la chaîne mimi, et
renvoie FAUX dans le cas contraire.
Remarque: Les critères de type TEXTE doivent être encadrés par des guillemets.
Un autre exemple: =SI($A$3<AUJOURDHUI();VRAI;FAUX)
Renvoie une valeur VRAI si la date saisie en A3 est antérieure à aujourd'hui.
Consultez le chapitre III pour visualiser d'autres exemples. L'article sur
les fonctions Excel
décrit aussi la mise en oeuvre des formules logiques.
Le bouton Format, dans la boîte de dialogue principale, permet de paramétrer la mise en
forme qui va être appliquée lorsque la condition sera remplie.
Vous pouvez spécifier la police, les bordures ou les motifs de la cellule en fonction de vos préférences.
L'objectif principal restant la mise en évidence des données qui répondent aux conditions.
Validez votre choix.
Vous pouvez ensuite visualiser la mise en forme spécifiée dans la fenêtre principale:
Le bouton Ajouter permet de créer une nouvelle MFC dans la cellule (ou la plage de cellules)
active.
Remarque:
Le nombre de conditions est limité à 3 par cellule.
Le bouton Supprimer permet d'ôter une MFC de la cellule (ou la plage de cellules) active.
Choisissez la ou les conditions à supprimer puis cliquez sur le bouton OK pour valider.
III. Informations complémentaires▲
Une mise en forme conditionnelle ne peut se référer à un autre classeur.
La mise en forme conditionnelle peut se référer à une autre feuille (du même classeur)
en nommant les cellules qui seront utilisées dans la formule:
Menu Insertion/Nom/Définir
Il n'est pas posssible d'appliquer une mise en forme conditionnelle en fonction d'un format de
cellule.
Il n'est pas possible de compter par macro les cellules répondant aux critères d'une MFC.
Une solution de remplacement consiste à recréer une fonction qui utilisera les
mêmes critères que la mise en forme conditionnelle.
Lorsque plusieurs conditions sont vraies, seule la mise en
forme de la première condition vraie est appliquée dans la mise en forme.
Il n'est pas possible de modifier les mises en forme conditionnelles ou en appliquer
de nouvelles pendant qu'un classeur est partagé.
Les MFC créées avant que le classeur ne soit partagé continuent de fonctionner.
Les dates et les heures sont évaluées sous forme de numéros de série dans les mises en forme
conditionnelles: 01/01/1900 = 1 , 09/03/2007 = 39150 ...
III-A. Copier la mise en forme▲
Vous pouvez reproduire facilement une MFC existante en effectuant un Copier/Coller:
Copiez la cellule qui contient la mise en forme.
Ensuite sélectionnez la cellule (ou la plage de cellules) qui doit recevoir la même mise en forme.
Effectuez un clic droit (ou utilisez le menu Edition/Collage spécial).
Choisissez l'option "Collage spécial" dans le menu contextuel.
Sélectionnez "Format".
Cliquez sur le bouton OK pour valider.
Vous pouvez aussi utiliser l'outil "Reproduire la mise en forme" 
pour dupliquer la MFC d'une cellule.
ATTENTION à bien prendre en compte les références relatives, absolues ou mixtes avant le collage
des formules sinon le résultat risque d'être erroné.
III-B. Les références▲
III-B-1. Les références relatives▲
Une référence relative (qui s'affiche sous la forme =A1+A2) est basée
sur la position relative de la cellule qui contient la formule et les références de cellules
spécifiées dans la formule.
Si la position de la cellule qui contient la formule change, la référence est modifiée. Si vous copiez
la formule dans d'autres lignes ou colonnes, la référence est automatiquement adaptée en conséquence.
Par exemple, si vous copiez une référence relative =A1+A2 contenue dans
la cellule B2 vers la cellule B3, la formule est automatiquement transformée
en =A2+A3.
Utilisez des références relatives si vous souhaitez adapter les références à chaque cellule
de la plage sélectionnée.
III-B-2. Les références absolues (Utilisation du symbole $)▲
Une référence de cellule absolue (qui s'affiche sous la forme =$A$1+$A$2)
spécifie des cellules se trouvant à un endroit fixe.
Si la position de la cellule qui contient la formule change, la référence absolue reste
inchangée.
Par exemple, si vous copiez une référence absolue =$A$1+$A$2 de la
cellule B2 vers la cellule B3, la formule reste la même dans les deux cellules.
Les formules utilisent des références relatives par défaut. Il faut donc les transformer en références
absolues manuellement en ajoutant les symboles $.
III-B-3. Les références mixtes▲
Une référence mixte comprend soit une colonne absolue et une ligne relative ($A1,$B1,...), soit une
ligne absolue et une colonne relative (A$1,B$1,...).
Par exemple, si vous copiez une référence mixte =A$1*2 de la cellule A2
vers la cellule B3, la formule est transformée en =B$1*2.
III-B-4. Exemple▲
Sélectionnez la plage de cellule A1:A10
Appliquez une condition "La formule est:" =A1=10
Choisissez une mise en forme "Motif" pour colorier le fond des cellules lorsque la condition sera VRAI.
Validez.
En procédant ainsi, la condition est VRAI pour chaque cellule de la sélection qui contient la valeur 10.
Si vous aviez indiqué une référence absolue: =$A$1=10,
la condition serait VRAI pour toutes les cellules de la sélection, lorsque la cellule A1 est égale à 10.
IV. Exemples▲
IV-A. Rechercher les cellules dotées de mises en forme conditionnelles▲
Pour retrouver toutes les cellules contenant des mises en forme conditionnelles, cliquez sur
n'importe quelle cellule dans la feuille.
Ensuite, Menu Edition / Atteindre
Cliquez sur le bouton Cellules
Sélectionnez l'option Formats conditionnels
Choisissez l'option "Toutes" pour rechercher l'ensemble des cellules contenant des mises
en forme conditionnelles.
Remarque: Sélectionnez l'option "Identiques" pour rechercher uniquement les cellules
contenant des mises en forme conditionnelles identiques à celles de la cellule selectionnée.
Cliquez sur le bouton OK pour valider.
IV-B. Identifier la valeur maximale dans une plage de cellules▲
Cet exemple identifie la valeur maximale de la plage de cellules A1:C10
Sélectionnez la plage A1:C10 puis appliquez cette formule dans la MFC.
=A1=MAX($A$1:$C$10)
IV-C. Masquer les erreurs dans les cellules▲
Les mises en formes conditionnelles peuvent aussi être utilisées pour masquer des valeurs, en appliquant
par exemple la même couleur au texte et au fond de la cellule.
Les deux exemples suivants montrent comment masquer les
erreurs contenues dans les
cellules.
Pour appliquer une mise en forme conditionnelle si la cellule A1 contient une erreur
Choisissez l'option "La formule est:" dans la MFC et saisissez:
=ESTERREUR(A1)
Ensuite appliquez le format couleur de police identique au fond de la cellule.
Masquer l'erreur type #N/A dans la cellule A1
Choisissez l'option "La formule est:" dans la MFC et saisissez:
=ESTNA(A1)
Ensuite appliquez le format couleur de police identique au fond de la cellule.
IV-D. Appliquer une mise en forme si la cellule A1 est non vide▲
Choisissez l'option "La formule est:" dans la MFC et saisissez:
=NON(ESTVIDE(A1))
IV-E. Rechercher la date la plus proche▲
Cet exemple met en évidence la date la plus proche d'aujourd'hui, dans la plage de cellule A1:A20.
Sélectionnez la plage A1:A20.
Pour retouver la date la plus proche future, choisissez l'option "La formule est:" dans la
MFC et saisissez:
=MAX(SI(AUJOURDHUI()-$A$1:$A$20<=0;AUJOURDHUI()-$A$1:$A$20))=AUJOURDHUI()-A1
Pour retrouver la date la plus proche passée, utilisez:
=MIN(SI(AUJOURDHUI()-$A$1:$A$20>=0;AUJOURDHUI()-$A$1:$A$20))=AUJOURDHUI()-A1
IV-F. Retrouver les cellules comprises entre deux dates▲
Dans cet exemple, les dates à retrouver sont dans la colonne A.
La date de début est indiquée dans la cellule B1, et la date de fin dans la cellule B2.
Sélectionnez la colonne A. Choisissez l'option "La formule est:" dans la MFC puis saisissez:
=ET(A1>=$B$1;A1<=$B$2)
IV-G. Identifier les doublons dans la colonne A▲
Choisissez l'option "La formule est:" dans la MFC et saisissez:
=NB.SI(A:A;A1)>1
IV-H. Une mise en forme en fonction de 2 critères dans une formule▲
Cet exemple identifie les employés dont le salaire dépasse 2000$ et dont les noms commence par F.
Les noms d'employés sont dans la colonne A et les salaires dans la colonne B.
Choisissez l'option "La formule est:" dans la MFC et saisissez:
=ET(DECALER(A1;0;1;1;1)>2000;GAUCHE(A1;1)="F")
IV-I. Appliquer une couleur sur une ligne complète▲
Dans cet exemple la condition sera VRAI si la cellule A1 = 10.
Sélectionnez la ligne à coloriser.
Dans la mise en forme conditionnelle, insérez "la formule est " =$A$1=10
Sélectionnez le motif à appliquer, puis validez.
IV-J. Appliquer une couleur sur toutes les lignes impaires de la feuille▲
Sélectionnez toutes les cellules de la feuille de calcul.
Ensuite, choisissez l'option "La formule est:" et saisissez:
=MOD(LIGNE();2)=1
Sélectionnez une couleur dans l'onglet "Motif" afin de spécifier le format de la mise en forme.
Cette astuce est pratique pour améliorer la lisibilité des feuilles utilisées comme base de données.
Pour appliquer la couleur sur les lignes paires, utilisez:
=MOD(LIGNE();2)=0
V. La manipulation des MFC par macro▲
Les mises en formes conditionnelles peuvent être mises en place et paramétrées par macro.
Vous pouvez par exemple afficher la boîte de dialogue principale de mise en forme conditionnelle:
Application.Dialogs(xlDialogConditionalFormatting).Show
L'exemple suivant ajoute une mise en forme conditionnelle dans la cellule A1.
With Range("A1")
'Supprime les MFC existantes
.FormatConditions.Delete
'Ajoute une condition (Vrai lorsque la cellule est non vide)
.FormatConditions.Add Type:=xlExpression, Formula1:="=NON(ESTVIDE($A$1))"
With .FormatConditions(1)
'Définit la couleur de fond de la cellule lorsque la condition sera vraie.
.Interior.ColorIndex = 15 'Gris
'Affecte le style "gras" à la police
.Font.Bold = True
'Voir aussi:
'.Font.Italic = True
'.Font.Strikethrough = True
'.Font.Underline = True
'Styles de ligne:
'xlNone, xlSolid, xlDash, xlDot, xlDashDot, xlDashDotDot, xlGray50, xlGray75, et xlGray25.
.Borders.LineStyle = xlContinuous
'Couleur bordure
.Borders.ColorIndex = 5 'Bleu
'Epaisseur trait de la bordure
.Borders.Weight = xlThin 'Autre constante disponible: xlHairline
End With
End With
L'argument xlExpression permet d'insérer une formule dans la MFC.
Utilisez l'argument xlCellValue pour que la mise en forme conditionnelle
soit basée sur l'option "La valeur de la cellule est".
La méthode Add renvoie une erreur si la cellule contient déjà 3 conditions.
Le tableau ci dessous récapitule les opérateurs disponibles pour les MFC.
Remarque: L'argument Operator est ignoré si le type de
condition xlExpression est utilisé.
| Opérateur | Traduction |
|---|---|
| xlBetween | Compris entre |
| xlNotBetween | Non compris entre |
| xlEqual | Egal à |
| xlNotEqual | Différent de |
| xlGreater | Supérieur à |
| xlLess | Inférieur à |
| xlGreaterEqual | Supérieur ou égal à |
| xlLessEqual | Inférieur ou égal à |
Cet exemple montre comment modifier la première condition dans la plage
A1:A10.
La procédure renvoie une erreur si aucune MFC n'est préalablement appliquée dans les cellules.
'Spécifie la condition "Est inférieur à 500"
Worksheets(1).Range("A1:A10").FormatConditions(1).Modify xlCellValue, xlLess, 500
La procédure suivante extrait toutes les mises en forme d'une cellule.
Vous pourrez l'utiliser pour récupérer les critères spécifiés et par exemple
tester les conditions par macro.
Sub Test()
'Récupère les conditions de la cellule D2
ExtraitConditions Range("D2")
End Sub
Sub ExtraitConditions(Cell As Range)
Dim Fc As FormatCondition
Dim Resultat As String
'Vérifie si la cellule contient une mise en forme conditionnelle
If Cell.FormatConditions.Count > 0 Then
'Boucle sur les MFC de la cellule
For Each Fc In Cell.FormatConditions
'Si la MFC est de type "La valeur de la cellule est"
If Fc.Type = xlCellValue Then
Select Case Fc.Operator
Case xlBetween
Resultat = "Compris entre " & Fc.Formula1 & " et " & Fc.Formula2
Case xlNotBetween
Resultat = "Non compris entre " & Fc.Formula1 & " et " & Fc.Formula2
Case xlEqual
Resultat = "Egal à " & Fc.Formula1
Case xlNotEqual
Resultat = "Différent de " & Fc.Formula1
Case xlGreater
Resultat = "Supérieur à " & Fc.Formula1
Case xlLess
Resultat = "Inférieur à " & Fc.Formula1
Case xlGreaterEqual
Resultat = "Supérieur ou égal à " & Fc.Formula1
Case xlLessEqual
Resultat = "Inférieur ou égal à " & Fc.Formula1
End Select
Else
'Si la MFC est de type "La formule est"
Resultat = "La formule est " & Fc.Formula1
End If
MsgBox Resultat
Next Fc
End If
End Sub
Lorsque vous réinitialisez une feuille de calcul, vous pouvez avoir besoin de supprimer toutes
les MFC rapidement.
Cette procédure supprime tous les formats conditionnels contenus dans la feuille:
Sub SupprimeConditionsFeuille()
Feuil1.Cells.FormatConditions.Delete
End Sub
VI. Remerciements▲
Merci à Loufab pour
sa relecture et ses remarques avisées.
VII. Téléchargement▲