Mise en forme Conditionnelle en excel

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
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:

Vba

Application.Dialogs(xlDialogConditionalFormatting).Show

L'exemple suivant ajoute une mise en forme conditionnelle dans la cellule A1.

Vba

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.

Vba

'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.

Vba

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:

Vba

Sub SupprimeConditionsFeuille() Feuil1.Cells.FormatConditions.Delete End Sub