Expliquer exactement pourquoi la mise en forme conditionnelle saute quand vous supprimes des lignes ou colonnes

Expliquer exactement pourquoi la mise en forme conditionnelle saute quand vous supprimes des lignes ou colonnes, surtout avec une formule comme :

=NB.SI.ENS('Param Agenda'!$AR$3:$AR$100; D8; 'Param Agenda'!$AR$3:$AR$100; "<>")>0

NB : la troisième solution plage nommer et la plus efficace 

 la première solution plage dynamique rend Excel long

✅ Le vrai problème

Lorsque tu supprimes des lignes ou colonnes, Excel réduit littéralement les plages de cellules utilisées dans la mise en forme conditionnelle.

➡️ Exemple :
Si ta règle utilise :
$AR$3:$AR$100
et que tu supprimes la ligne 10, Excel ajuste automatiquement →
$AR$3:$AR$99

Si tu supprimes plusieurs lignes dans cette plage, elle devient incohérente ou fragmentée — et Excel réorganise alors l’ordre des règles, ce qui donne l’impression qu’elles “disparaissent”.

Ce comportement est connu, agaçant… et malheureusement normal dans Excel.

🎯 Ce qui cause le changement d’ordre

1. La plage S'applique à se casse en plusieurs morceaux (ex. $D$8:$D$20,$D$45:$D$70)
2. Excel tente de rééquilibrer les règles, et change automatiquement :
   • l’ordre
   • la priorité
   • parfois même la formule si elle n’est plus cohérente

La présence de NB.SI.ENS n’est pas un problème en soi : c’est la plage fixe $AR$3:$AR$100 combinée aux suppressions qui provoque tout.

🛠️ Solution SIMPLE et définitive

✔ 1. Remplace la plage fixe par une plage dynamique

Au lieu de ceci :

'Param Agenda'!$AR$3:$AR$100

Utilise :

'Param Agenda'!$AR:$AR

➡️ La colonne entière
➡️ Ne sera plus impactée par suppression de lignes
➡️ La mise en forme ne change plus d’ordre

👉 Formule finale :

=NB.SI.ENS('Param Agenda'!$AR:$AR; D8; 'Param Agenda'!$AR:$AR; "<>")>0

---

✔ 2. OU utilise une table (Ctrl + T)

Si ta plage "Param Agenda" est convertie en tableau :

• Elle s’adapte automatiquement aux suppressions
• Les règles ne se fragmentent plus
• Excel ne change plus l’ordre des règles

---

✔ 3. OU verrouille la plage avec un nom défini

1. Formules > Gestionnaire de noms
2. Nouveau nom : PlageAgenda
3. Réfère à :

   =DECALER('Param Agenda'!$AR$3;0;0;NBVAL('Param Agenda'!$AR:$AR))
   

4. Remplace la plage dans ta règle par : PlageAgenda

---

💡 Important

Ce que tu observes est fréquent dans les classeurs où l'on supprime souvent des lignes.
La plage dynamique est la solution la plus simple et la plus stable.