Formatage conditionnel Excel
La mise en forme conditionnelle Excel vous permet de définir des règles qui déterminent la mise en forme des cellules.
Par exemple, vous pouvez créer une règle qui met en évidence les cellules qui répondent à certains critères. Les exemples comprennent:
- Nombres compris dans une certaine plage (ex. Moins de 0).
- Les 10 premiers éléments d'une liste.
- Création d'une « carte thermique ».
- Règles « basées sur des formules » pour pratiquement toutes les mises en forme conditionnelles.
Dans Excel, la mise en forme conditionnelle se trouve dans le ruban sous Accueil > Styles (ALT > H > L).
Pour créer votre propre règle, cliquez sur « Nouvelle règle » et une nouvelle fenêtre apparaîtra :
Formatage conditionnel en VBA
Toutes ces fonctionnalités de mise en forme conditionnelle sont accessibles à l'aide de VBA.
Notez que lorsque vous configurez la mise en forme conditionnelle à partir du code VBA, vos nouveaux paramètres apparaîtront dans la fenêtre de mise en forme conditionnelle frontale d'Excel et seront visibles pour l'utilisateur. L'utilisateur pourra les modifier ou les supprimer à moins que vous n'ayez verrouillé la feuille de calcul.
Les règles de mise en forme conditionnelle sont également enregistrées lors de l'enregistrement de la feuille de calcul
Les règles de mise en forme conditionnelle s'appliquent spécifiquement à une feuille de calcul particulière et à une plage de cellules particulière. S'ils sont nécessaires ailleurs dans le classeur, ils doivent également être configurés sur cette feuille de calcul.
Utilisations pratiques du formatage conditionnel en VBA
Vous pouvez avoir une grande quantité de données brutes importées dans votre feuille de calcul à partir d'un fichier CSV (valeurs séparées par des virgules) ou d'une table ou d'une requête de base de données. Cela peut se retrouver dans un tableau de bord ou un rapport, avec des chiffres changeants importés d'une période à l'autre.
Lorsqu'un nombre change et se situe en dehors d'une plage acceptable, vous pouvez le mettre en évidence, par ex. la couleur d'arrière-plan de la cellule en rouge, et vous pouvez configurer la mise en forme conditionnelle. De cette façon, l'utilisateur sera instantanément attiré par ce numéro et pourra alors rechercher pourquoi cela se produit.
Vous pouvez utiliser VBA pour activer ou désactiver la mise en forme conditionnelle. Vous pouvez utiliser VBA pour effacer les règles sur une plage de cellules ou les réactiver. Il peut y avoir une situation où il y a une parfaitement bonne raison pour un nombre inhabituel, mais lorsque l'utilisateur présente le tableau de bord ou rapporte à un niveau supérieur de gestion, il souhaite pouvoir supprimer les « sonnettes d'alarme ».
De plus, sur les données brutes importées, vous voudrez peut-être mettre en évidence les endroits où les nombres sont ridiculement grands ou ridiculement petits. La plage de données importée a généralement une taille différente pour chaque période, vous pouvez donc utiliser VBA pour évaluer la taille de la nouvelle plage de données et insérer une mise en forme conditionnelle uniquement pour cette plage.
Vous pouvez également avoir une situation où il existe une liste triée de noms avec des valeurs numériques contre chacun, par ex. salaire des employés, notes d'examen. Avec la mise en forme conditionnelle, vous pouvez utiliser des couleurs graduées pour aller du plus haut au plus bas, ce qui est très impressionnant à des fins de présentation.
Cependant, la liste des noms ne sera pas toujours de taille statique et vous pouvez utiliser le code VBA pour actualiser l'échelle des couleurs graduées en fonction des modifications de la taille de la plage.
Un exemple simple de création d'un format conditionnel sur une plage
Cet exemple configure la mise en forme conditionnelle pour une plage de cellules (A1:A10) sur une feuille de calcul. Si le nombre dans la plage est compris entre 100 et 150, la couleur d'arrière-plan de la cellule sera rouge, sinon elle n'aura pas de couleur.
| 1234567891011121314 | Exemple de formatage sous-conditionnel()« Définir la plageDim MyRange As RangeDéfinir MyRange = Range ("A1:A10")« Supprimer la mise en forme conditionnelle existante de la plageMyRange.FormatConditions.Delete« Appliquer la mise en forme conditionnelleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formule1 :="=100", Formule2 :="=150"MaPlage.FormatConditions(1).Intérieur.Couleur = RVB (255, 0, 0)Fin du sous-marin |
Notez que nous définissons d'abord la plage MaPlage pour appliquer une mise en forme conditionnelle.
Ensuite, nous supprimons toute mise en forme conditionnelle existante pour la plage. C'est une bonne idée pour éviter que la même règle soit ajoutée à chaque fois que le code est exécuté (bien sûr, cela ne sera pas approprié dans toutes les circonstances).
Les couleurs sont données par des valeurs numériques. C'est une bonne idée d'utiliser la notation RVB (Rouge, Vert, Bleu) pour cela. Vous pouvez utiliser des constantes de couleur standard pour cela, par ex. vbRed, vbBlue, mais vous êtes limité à huit choix de couleurs.
Il y a plus de 16,7 millions de couleurs disponibles, et en utilisant RVB, vous pouvez accéder à toutes. C'est beaucoup plus facile que d'essayer de se rappeler quel nombre va avec quelle couleur. Chacun des trois numéros de couleur RVB est compris entre 0 et 255.
Notez que le paramètre « xlBetween » est inclusif, donc les valeurs de cellule de 100 ou 150 satisferont la condition.
Formatage multi-conditionnel
Vous pouvez définir plusieurs règles conditionnelles dans votre plage de données afin que toutes les valeurs d'une plage soient couvertes par des conditions différentes :
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter la première règleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formule1 :="=100", Formule2 :="=150"MyRange.FormatConditions(1).Intérieur.Couleur = RVB (255, 0, 0)'Ajouter une deuxième règleMyRange.FormatConditions.Add Type :=xlCellValue, Opérateur :=xlLess, _Formule1 :="=100"MyRange.FormatConditions(2).Interior.Color = vbBlue'Ajouter une troisième règleMyRange.FormatConditions.Add Type :=xlCellValue, Opérateur :=xlGreater, _Formule1 :="=150"MyRange.FormatConditions(3).Interior.Color = vbYellowFin du sous-marin |
Cet exemple configure la première règle comme précédemment, avec la couleur de cellule rouge si la valeur de la cellule est comprise entre 100 et 150.
Deux autres règles sont alors ajoutées. Si la valeur de la cellule est inférieure à 100, la couleur de la cellule est bleue et si elle est supérieure à 150, la couleur de la cellule est jaune.
Dans cet exemple, vous devez vous assurer que toutes les possibilités de nombres sont couvertes et que les règles ne se chevauchent pas.
Si les cellules vides se trouvent dans cette plage, elles s'afficheront en bleu, car Excel les considère toujours comme ayant une valeur inférieure à 100.
Le moyen de contourner cela est d'ajouter une autre condition en tant qu'expression. Cela doit être ajouté comme première règle de condition dans le code. Il est très important, lorsqu'il existe plusieurs règles, d'obtenir le bon ordre d'exécution, sinon les résultats peuvent être imprévisibles.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter la première règleMyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _"=LEN(AJUSTER(A1))=0"MyRange.FormatConditions(1).Interior.Pattern = xlNone'Ajouter une deuxième règleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formule1 :="=100", Formule2 :="=150"MaPlage.FormatConditions(2).Intérieur.Couleur = RVB (255, 0, 0)'Ajouter une troisième règleMyRange.FormatConditions.Add Type :=xlCellValue, Opérateur :=xlLess, _Formule1 :="=100"MyRange.FormatConditions(3).Interior.Color = vbBlue'Ajouter une quatrième règleMyRange.FormatConditions.Add Type :=xlCellValue, Opérateur :=xlGreater, _Formule1 :="=150"MaPlage.FormatConditions(4).Intérieur.Couleur = RVB (0, 255, 0)Fin du sous-marin |
Cela utilise le type de xlExpression, puis utilise une formule Excel standard pour déterminer si une cellule est vide au lieu d'une valeur numérique.
L'objet FormatConditions fait partie de l'objet Range. Il agit de la même manière qu'une collection avec l'index commençant à 1. Vous pouvez parcourir cet objet en utilisant une boucle For… Next ou For… Each.
Suppression d'une règle
Parfois, vous devrez peut-être supprimer une règle individuelle dans un ensemble de règles multiples si elle ne correspond pas aux exigences en matière de données.
| 12345678910111213 | Sub DeleteConditionalFormattingExample()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter la première règleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formule1 :="=100", Formule2 :="=150"MyRange.FormatConditions(1).Intérieur.Couleur = RVB (255, 0, 0)'Supprimer la règleMaPlage.FormatConditions(1).DeleteFin du sous-marin |
Ce code crée une nouvelle règle pour la plage A1:A10, puis la supprime. Vous devez utiliser le bon numéro d'index pour la suppression, alors vérifiez sur « Gérer les règles » sur le front-end Excel (cela affichera les règles dans l'ordre d'exécution) pour vous assurer que vous obtenez le bon numéro d'index. Notez qu'il n'y a pas de fonction d'annulation dans Excel si vous supprimez une règle de mise en forme conditionnelle dans VBA, contrairement à si vous le faites via le frontal Excel.
Modification d'une règle
Étant donné que les règles sont un ensemble d'objets basés sur une plage spécifiée, vous pouvez facilement modifier des règles particulières à l'aide de VBA. Les propriétés réelles une fois la règle ajoutée sont en lecture seule, mais vous pouvez utiliser la méthode Modify pour les modifier. Les propriétés telles que les couleurs sont en lecture/écriture.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter la première règleMyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _Formule1 :="=100", Formule2 :="=150"MyRange.FormatConditions(1).Intérieur.Couleur = RVB (255, 0, 0)'Changer de règleMyRange.FormatConditions(1).Modify xlCellValue, xlLess, "10"'Changer la couleur de la règleMyRange.FormatConditions(1).Interior.Color = vbGreenFin du sous-marin |
Ce code crée un objet de plage (A1:A10) et ajoute une règle pour les nombres compris entre 100 et 150. Si la condition est vraie, la couleur de la cellule passe au rouge.
Le code modifie ensuite la règle en nombres inférieurs à 10. Si la condition est vraie, la couleur de la cellule passe maintenant au vert.
Utilisation d'un schéma de couleurs gradué
La mise en forme conditionnelle Excel permet d'utiliser des couleurs graduées sur une plage de nombres en ordre croissant ou décroissant.
Ceci est très utile lorsque vous disposez de données telles que les chiffres de vente par zone géographique, les températures de la ville ou les distances entre les villes. En utilisant VBA, vous avez l'avantage supplémentaire de pouvoir choisir votre propre palette de couleurs graduées, plutôt que celles standard proposées sur le front-end Excel.
| 1234567891011121314151617181920212223242526272829 | Couleurs sous-graduées()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Définir le type d'échelleMyRange.FormatConditions.AddColorScale ColorScaleType:=3'Sélectionnez la couleur pour la valeur la plus basse de la plageMyRange.FormatConditions(1).ColorScaleCriteria(1).Type = _xlConditionValueLowestValueAvec MyRange.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Couleur = 7039480Terminer par'Sélectionnez la couleur pour les valeurs moyennes de la plageMyRange.FormatConditions(1).ColorScaleCriteria(2).Type = _xlConditionValuePercentileMyRange.FormatConditions(1).ColorScaleCriteria(2).Value = 50'Sélectionnez la couleur pour le milieu de la plageAvec MyRange.FormatConditions(1).ColorScaleCriteria(2).FormatColor.Couleur = 8711167Terminer par'Sélectionnez la couleur pour la valeur la plus élevée de la gammeMyRange.FormatConditions(1).ColorScaleCriteria(3).Type = _xlConditionValueHighestValueAvec MyRange.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Couleur = 8109667Terminer parFin du sous-marin |
Lorsque ce code est exécuté, il gradue les couleurs des cellules en fonction des valeurs croissantes de la plage A1:A10.
C'est une façon très impressionnante d'afficher les données et attirera certainement l'attention des utilisateurs.
Formatage conditionnel pour les valeurs d'erreur
Lorsque vous avez une énorme quantité de données, vous pouvez facilement manquer une valeur d'erreur dans vos différentes feuilles de calcul. Si cela est présenté à un utilisateur sans être résolu, cela pourrait entraîner de gros problèmes et l'utilisateur pourrait perdre confiance dans les chiffres. Cela utilise un type de règle xlExpression et une fonction Excel de IsError pour évaluer la cellule.
Vous pouvez créer du code pour que toutes les cellules contenant des erreurs aient une couleur de cellule rouge :
| 1234567891011 | Sub ErrorConditionalFormattingExample()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter une règle d'erreurMyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=IsError(A1)=true"'Régler la couleur intérieure sur le rougeMaPlage.FormatConditions(1).Intérieur.Couleur = RVB (255, 0, 0)Fin du sous-marin |
Mise en forme conditionnelle pour les dates dans le passé
Vous pouvez avoir importé des données où vous souhaitez mettre en évidence des dates qui sont dans le passé. Un exemple de ceci pourrait être un rapport de débiteurs où vous voulez que toutes les anciennes dates de factures de plus de 30 jours se démarquent.
Ce code utilise le type de règle xlExpression et une fonction Excel pour évaluer les dates.
| 1234567891011 | Sub DateInPastConditionalFormattingExample()Dim MyRange As Range'Créer un objet de plage basé sur une colonne de datesDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter une règle d'erreur pour les dates passéesMyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=Now()-A1 > 30"'Régler la couleur intérieure sur le rougeMyRange.FormatConditions(1).Intérieur.Couleur = RVB (255, 0, 0)Fin du sous-marin |
Ce code prendra une plage de dates dans la plage A1:A10 et définira la couleur de la cellule sur rouge pour toute date située plus de 30 jours dans le passé.
Dans la formule utilisée dans la condition, Now() donne la date et l'heure actuelles. Cela continuera à recalculer chaque fois que la feuille de calcul est recalculée, de sorte que la mise en forme changera d'un jour à l'autre.
Utilisation des barres de données dans la mise en forme conditionnelle VBA
Vous pouvez utiliser VBA pour ajouter des barres de données à une plage de nombres. Ce sont presque comme des mini-graphiques et donnent une vue instantanée de la taille des nombres les uns par rapport aux autres. En acceptant des valeurs par défaut pour les barres de données, le code est très facile à écrire.
| 123456 | Sub DataBarFormattingExample()Dim MyRange As RangeDéfinir MyRange = Range ("A1:A10")MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarFin du sous-marin |
Vos données ressembleront à ceci sur la feuille de calcul :
Utilisation d'icônes dans la mise en forme conditionnelle VBA
Vous pouvez utiliser la mise en forme conditionnelle pour placer des icônes à côté de vos nombres dans une feuille de calcul. Les icônes peuvent être des flèches ou des cercles ou diverses autres formes. Dans cet exemple, le code ajoute des icônes de flèche aux nombres en fonction de leurs valeurs de pourcentage :
| 12345678910111213141516171819202122232425 | Sous-ensembles d'icônesExample()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter un jeu d'icônes à l'objet FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Définir l'icône sur les flèches - condition 1Avec MyRange.FormatConditions(1).IconSet = ActiveWorkbook.IconSets(xl3Flèches)Terminer par'définir les critères d'icône pour la valeur de pourcentage requise - condition 2Avec MyRange.FormatConditions(1).IconCriteria(2).Type = xlConditionValuePercent.Valeur = 33.Operator = xlGreaterEqualTerminer par'définir les critères d'icône pour la valeur de pourcentage requise - condition 3Avec MyRange.FormatConditions(1).IconCriteria(3).Type = xlConditionValuePercent.Valeur = 67.Operator = xlGreaterEqualTerminer parFin du sous-marin |
Cela donnera une vue instantanée indiquant si un nombre est élevé ou faible. Après avoir exécuté ce code, votre feuille de calcul ressemblera à ceci :
Utilisation de la mise en forme conditionnelle pour mettre en évidence les cinq premiers
Vous pouvez utiliser le code VBA pour mettre en évidence les 5 premiers numéros d'une plage de données. Vous utilisez un paramètre appelé « AddTop10 », mais vous pouvez ajuster le numéro de rang dans le code à 5. Un utilisateur peut souhaiter voir les nombres les plus élevés dans une plage sans avoir à trier les données au préalable.
| 1234567891011121314151617181920212223 | Sous Top5Exemple()Dim MyRange As Range'Créer un objet de plageDéfinir MyRange = Range ("A1:A10")'Supprimer les formats conditionnels précédentsMyRange.FormatConditions.Delete'Ajouter une condition Top10MyRange.FormatConditions.AddTop10Avec MyRange.FormatConditions(1)'Définir le paramètre de haut en bas.TopBottom = xlTop10Top'Définir le top 5 uniquement.Rang = 5Terminer parAvec MyRange.FormatConditions(1).Font'Définir la couleur de la police.Couleur = -16383844Terminer parAvec MyRange.FormatConditions(1).Intérieur'Définir la couleur de fond de cellule.Couleur = 13551615Terminer parFin du sous-marin |
Les données de votre feuille de calcul ressembleraient à ceci après avoir exécuté le code :
Notez que la valeur de 145 apparaît deux fois, donc six cellules sont mises en surbrillance.
Signification des paramètres StopIfTrue et SetFirstPriority
StopIfTrue est important si une plage de cellules comporte plusieurs règles de mise en forme conditionnelle. Une seule cellule de la plage peut satisfaire à la première règle, mais elle peut également satisfaire aux règles suivantes. En tant que développeur, vous souhaiterez peut-être qu'il affiche le formatage uniquement pour la première règle à laquelle il s'agit. D'autres critères de règles peuvent se chevaucher et peuvent apporter des modifications involontaires s'ils sont autorisés à continuer dans la liste des règles.
La valeur par défaut de ce paramètre est True, mais vous pouvez la modifier si vous souhaitez que toutes les autres règles de cette cellule soient prises en compte :
| 1 | Ma Gamme. FormatConditions(1).StopIfTrue = False |
Le paramètre SetFirstPriority détermine si cette règle de condition sera évaluée en premier lorsqu'il existe plusieurs règles pour cette cellule.
| 1 | Ma Gamme. FormatConditions(1).SetFirstPriority |
Cela déplace la position de cette règle à la position 1 dans la collection de conditions de format, et toutes les autres règles seront déplacées vers le bas avec des numéros d'index modifiés. Méfiez-vous si vous apportez des modifications aux règles dans le code à l'aide des numéros d'index. Vous devez vous assurer que vous modifiez ou supprimez la bonne règle.
Vous pouvez modifier la priorité d'une règle :
| 1 | Ma Gamme. FormatConditions(1).Priorité=3 |
Cela changera les positions relatives de toutes les autres règles dans la liste des formats conditionnels.
Utilisation de la mise en forme conditionnelle faisant référence à d'autres valeurs de cellule
C'est une chose que la mise en forme conditionnelle Excel ne peut pas faire. Cependant, vous pouvez créer votre propre code VBA pour ce faire.
Supposons que vous ayez une colonne de données et que dans la cellule adjacente à chaque nombre, il y ait du texte qui indique quelle mise en forme doit avoir lieu sur chaque nombre.
Le code suivant parcourt votre liste de nombres, recherche dans la cellule adjacente le formatage du texte, puis formate le nombre selon les besoins :
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting()'Créer des variables pour contenir le nombre de lignes pour les données tabulairesDim RRow aussi longtemps, N aussi long'Capturer le nombre de lignes dans la plage de données tabulairesRRow = ActiveSheet.UsedRange.Rows.Count'Itérer à travers toutes les lignes de la plage de données tabulairesPour N = 1 à RRow'Utilisez une instruction Select Case pour évaluer la mise en forme en fonction de la colonne 2Sélectionnez Case ActiveSheet.Cells(N, 2).Value'Tourner la couleur intérieure en bleuEtui "Bleu"ActiveSheet.Cells(N, 1).Interior.Color = vbBlue'Tourner la couleur intérieure en rougeCas "Rouge"ActiveSheet.Cells(N, 1).Interior.Color = vbRed'Tourner la couleur intérieure en vertCas "Vert"ActiveSheet.Cells(N, 1).Interior.Color = vbGreenFin de la sélectionN suivantFin du sous-marin |
Une fois ce code exécuté, votre feuille de calcul ressemblera désormais à ceci :
Les cellules auxquelles il est fait référence pour la mise en forme peuvent se trouver n'importe où sur la feuille de calcul, ou même sur une autre feuille de calcul dans le classeur. Vous pouvez utiliser n'importe quelle forme de texte pour faire une condition pour le formatage, et vous n'êtes limité que par votre imagination dans les utilisations que vous pourriez faire de ce code.
Opérateurs pouvant être utilisés dans les instructions de mise en forme conditionnelle
Comme vous l'avez vu dans les exemples précédents, les opérateurs sont utilisés pour déterminer comment les valeurs de condition seront évaluées, par ex. xlEntre.
Il existe un certain nombre de ces opérateurs qui peuvent être utilisés, selon la manière dont vous souhaitez spécifier vos critères de règle.
| Nom | Valeur | La description |
| xlEntre | 1 | Entre. Peut être utilisé uniquement si deux formules sont fournies. |
| xlÉgal | 3 | Égal. |
| xlPlus | 5 | Plus grand que. |
| xlSupérieurÉgal | 7 | Plus grand ou égal à. |
| xlMoins | 6 | Moins que. |
| xlMoinsÉgal | 8 | Inférieur ou égal à. |
| xlPasEntre | 2 | Pas entre. Peut être utilisé uniquement si deux formules sont fournies. |
| xlPasÉgal | 4 | Inégal. |
