La mise en forme conditionnelle en VBA Excel — des règles qui restent vivantes, pas une boucle qui se périme

En bref — Une règle de mise en forme conditionnelle est une instruction permanente que vous donnez à Excel une seule fois ; Excel la réexécute à chaque modification. Une boucle For Each ... Interior.Color est une photographie — correcte à l'instant où elle s'exécute, périmée à l'instant où une valeur change. Vous ajoutez les règles via la collection FormatConditions d'une plage. Deux choses piègent tout le monde : les règles s'empilent si vous ne faites pas Delete avant Add, et dans une règle par expression, la référence doit être $D2 (colonne verrouillée, ligne libre) sous peine de surligner les mauvaises cellules.

Dim rng As Range: Set rng = ThisWorkbook.Worksheets("Sales").Range("A2:F1000")
rng.FormatConditions.Delete                              ' effacer d'abord les anciennes regles (idempotent)
With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$D2>1000")
    .Interior.Color = RGB(198, 239, 206)                 ' toute la ligne passe au vert quand la colonne D > 1000
End With

Colorer une cellule depuis VBA a deux modes complètement différents, et choisir le mauvais est un bug discret. Vous pouvez régler Interior.Color dans une boucle — une passe de peinture ponctuelle — ou vous pouvez ajouter une règle et laisser Excel posséder la coloration pour toujours. Ce guide traite du second, parce que c'est celui qui reste correct quand les données changent, et celui pour lequel les gens dégainent une boucle fragile à la place.

Ce que vous allez apprendre

Le modèle mental — une règle est un miroir vivant, une boucle est une photographie
Ajouter une règle via la collection FormatConditions
L'habitude qui la garde idempotente — Delete avant Add
Les deux types de règle que vous utilisez vraiment — xlCellValue et xlExpression
Le piège des références — pourquoi le surlignage de ligne entière exige $D2, pas D2 ni $D$2
Quand une boucle statique reste le bon outil

Le modèle mental : un miroir vivant, pas une photographie

Surligner les lignes en retard avec une boucle produit un instantané. Il est correct à l'instant où il s'exécute et faux à l'instant où quelqu'un modifie une date d'échéance — la couleur ne bouge pas, car rien ne relance la boucle :

' Instantane : correct maintenant, perime apres la prochaine modification.
Dim c As Range
For Each c In ws.Range("D2:D1000")
    If c.Value > 1000 Then c.EntireRow.Interior.Color = RGB(198, 239, 206)
Next c

Une règle de mise en forme conditionnelle est différente par nature. Vous décrivez la condition une seule fois, la confiez à Excel, et Excel la réévalue à chaque recalcul, pour toujours. Changez une valeur et la couleur suit dans le même instant. C'est toute la raison d'être de la mise en forme conditionnelle en code : une boucle peint, une règle promet. Une fois que vous voyez la coloration comme « qui a la charge de la garder correcte — moi, ou Excel », le choix entre les deux modes cesse d'être une question de style pour devenir une question d'exactitude.

Ajouter une règle : la collection FormatConditions

Chaque plage porte une collection FormatConditions. Vous lui Add-ez une condition ; Add renvoie la nouvelle condition, dont vous réglez ensuite les .Interior, .Font et .Borders :

Dim rng As Range: Set rng = ws.Range("B2:B1000")
With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
    .Interior.Color = RGB(255, 199, 206)   ' remplissage rouge quand la valeur est negative
    .Font.Color = RGB(156, 0, 6)
End With

La collection vit sur la plage à laquelle la règle s'applique — B2:B1000 ici — si bien que la règle et sa portée se posent d'un même souffle. .Add prend le Type de règle et (pour une règle de valeur) un Operator et un ou deux seuils Formula1/Formula2. Tout le reste consiste simplement à mettre en forme l'objet condition qu'elle a renvoyé.

L'habitude qui compte le plus : Delete avant Add

Voici le mode de défaillance qui rattrape tôt ou tard chaque macro de mise en forme conditionnelle. .Add ne remplace pas — il ajoute à la suite. Lancez la macro deux fois et la plage a deux règles identiques ; lancez-la dans une boucle sur les feuilles et vous en obtenez des centaines, chacune un petit coût de performance et un cauchemar à démêler à la main :

rng.FormatConditions.Delete                 ' <- la seule ligne qui garde ceci idempotent
With rng.FormatConditions.Add(...)          ' desormais exactement une regle, a chaque execution
    ...
End With

Rendez le Delete-avant-Add réflexe. C'est la différence entre une macro que vous pouvez lancer mille fois avec le même résultat et une qui accumule silencieusement des scories. Si vous devez conserver des règles préexistantes que l'utilisateur a ajoutées à la main, supprimez plus chirurgicalement en parcourant FormatConditions et en n'ôtant que les vôtres — mais pour une macro qui possède la mise en forme de la plage, un Delete propre en premier est le défaut honnête.

Les deux types de règle que vous utilisez vraiment

Il existe plusieurs valeurs de Type, mais deux portent presque tout le travail réel :

xlCellValue — compare la valeur de cette cellule. Operator:=xlLess, xlGreater, xlBetween, xlEqual. C'est le cas simple « colorer la cellule d'après son propre nombre ».
xlExpression — évalue une formule qui renvoie TRUE/FALSE. C'est le puissant : il peut référencer d'autres colonnes, et c'est ainsi que vous colorez une ligne entière d'après un seul champ.

' Regle de valeur : colorer une cellule en rouge quand sa propre valeur est sous zero.
rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"

' Regle par expression : colorer TOUTE la ligne quand la colonne D depasse 1000.
ws.Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000"

Les extras — AddDatabar, AddColorScale, AddIconSetCondition — sont la même collection avec des visuels plus riches, mais si vous comprenez valeur-contre-expression, vous comprenez le modèle.

Le piège des références : pourquoi le surlignage de ligne entière exige $D2

C'est le bug numéro un « ma règle surligne les mauvaises cellules », et c'est de la pure mécanique de tableur. Une formule xlExpression est évaluée relativement à la cellule en haut à gauche de la plage d'application, puis Excel la promène sur chaque cellule comme s'il recopiait une formule. Ce sont donc les signes dollar qui décident de ce qui bouge :

=$D2>1000 — colonne verrouillée, ligne libre. Chaque cellule d'une ligne donnée teste la colonne D de cette ligne, si bien que la ligne entière s'allume ensemble. C'est ce que vous voulez pour surligner une ligne.
=D2>1000 — rien de verrouillé. La référence dérive aussi par colonne, donc la ligne 2 teste D2, mais la colonne B de la ligne 2 teste E2, et tout se brouille.
=$D$2>1000 — tout verrouillé. Chaque cellule de toute la plage teste l'unique cellule D2 — si bien que le bloc entier est allumé ou éteint d'un seul tenant.

Ratez l'ancrage et la règle « fonctionne » (aucune erreur) mais colore n'importe quoi. Le remède est d'y penser exactement comme à une formule recopiée : verrouillez la colonne que vous testez, laissez la ligne relative.

Quand une boucle statique reste juste

Les règles ne sont pas toujours la réponse. Employez une simple boucle Interior.Color quand la couleur ne doit pas suivre les données — un rapport ponctuel que vous vous apprêtez à figer et exporter en PDF, où vous voulez le surlignage cuit dans la masse et immuable même après qu'on a modifié une cellule. Dans ce cas, une règle vivante est le mauvais outil : elle continuerait de recolorer un document censé être un instantané figé.

Le jugement est une ligne nette : si la couleur doit rester correcte au fil des modifications de la feuille, utilisez une règle ; si la couleur doit se figer telle qu'elle est maintenant, utilisez une boucle. La plupart du temps, vous voulez la règle — ce qui explique justement pourquoi dégainer la boucle par réflexe est l'erreur à désapprendre.

Comment ExcelMaster aide

La mise en forme conditionnelle en code a trois pièges discrets : des règles qui s'empilent parce que rien ne les supprime d'abord, une règle par expression ancrée $D$2 ou D2 au lieu de $D2 qui colore donc les mauvaises cellules, et une boucle fragile employée là où une règle avait sa place, si bien que le surlignage se périme à la modification suivante. Chacun s'exécute sans erreur et paraît faux plus tard.

ExcelMaster vous laisse décrire le résultat — « surligne les lignes où le total dépasse 1000 », « ombre les négatifs en rouge », « signale les éléments en retard et garde ça vivant ». Il écrit un FormatConditions.Delete avant d'Add-er pour que la macro soit idempotente, ancre les règles par expression en $D2 pour un surlignage de ligne entière correct, et ne descend à une boucle statique que lorsque vous voulez réellement un rapport figé. Vous gardez le classeur et le code — et la couleur suit les données comme vous l'entendiez.

Questions fréquentes

Comment ajouter une mise en forme conditionnelle en VBA ?

Ajoutez une règle à la collection FormatConditions d'une plage : Range("B2:B1000").FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0", puis réglez la mise en forme de la condition renvoyée (.Interior.Color, .Font.Color). La règle vit sur la plage à laquelle elle s'applique, et Excel la réévalue automatiquement dès que les données changent.

Pourquoi mes règles de mise en forme conditionnelle se multiplient-elles ?

Parce que FormatConditions.Add ajoute à la suite plutôt que de remplacer, si bien que chaque exécution ajoute une copie de plus. Appelez Range(...).FormatConditions.Delete avant d'Add-er pour garder la macro idempotente — exactement une règle, quel que soit le nombre de lancements. Pour effacer toute la feuille, utilisez Cells.FormatConditions.Delete.

Comment surligner une ligne entière avec la mise en forme conditionnelle en VBA ?

Utilisez une règle par expression sur la plage de la ligne entière et verrouillez la colonne testée, pas la ligne : Range("A2:F1000").FormatConditions.Add Type:=xlExpression, Formula1:="=$D2>1000". La référence $D2 (colonne verrouillée, ligne relative) fait que chaque ligne teste sa propre colonne D, si bien que la ligne complète se colore ensemble. $D$2 ou D2 surligneront les mauvaises cellules.

Quelle est la différence entre xlCellValue et xlExpression ?

xlCellValue compare chaque cellule à un seuil avec un Operator (xlLess, xlGreater, xlBetween) — parfait pour colorer une cellule d'après sa propre valeur. xlExpression évalue une formule renvoyant TRUE/FALSE et peut référencer d'autres colonnes, ce qui permet de colorer une ligne entière d'après un seul champ. Utilisez les règles par expression pour tout ce qui croise plusieurs colonnes.

Faut-il utiliser la mise en forme conditionnelle ou une boucle VBA pour colorer des cellules ?

Utilisez une règle quand la couleur doit rester correcte au fil des modifications de la feuille — Excel réévalue une règle FormatConditions à chaque changement. N'utilisez une boucle For Each ... Interior.Color que pour un rapport ponctuel et statique que vous comptez figer et exporter, où vous voulez le surlignage cuit dans la masse et non réappliqué après des modifications ultérieures.

Testé dans

Testé dans : Excel 365 (Windows 11), VBA 7.1 — dernière vérification le 08/09/2026.

Guides associés : VBA Cell Color · VBA ColorIndex · VBA RGB · VBA Font · VBA For Each