XL 2010 Mise en forme conditionnelle dynamique

[MFuzier]

Bonjour,
Je souhaiterais que dans la formule "=ET($AT2>(AUJOURDHUI()+14);$AT2<>""non réalisé"";$AU2="""")" du code ci-dessous, AT2 puisse être remplacé par une variable telle que MyAdresscelluledépart sans que la formule de la cellule ne devienne ET(MyAdresscelluledépart>(AUJOURDHUI()+14);$AT2<>""non réalisé"";$AU2="""")"

Range(MyAdresstout).Select 'Sélectionne toute une colonne
Range(MyAdresscelluledépart).Activate 'Sélectionne la première cellule où intégrer la MAJ conditionnelle
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ET($AT2>(AUJOURDHUI()+14);$AT2<>""non réalisé"";$AU2="""")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With

ou alors transformer le code ET(....) en du code VBA
Merci à vous

 

[TooFatBoy]

Bonjour,

La formule que tu souhaites modifier est une chaîne de caractères, donc tu peux la manipuler en tant que telle.

Je n'ai pas testé, mais peut-être qu'un code de ce genre pourrait fonctionner :

With Range(MyAdresstout)

    Ad1=Range(MyAdresscelluledépart).Address(RowAbsolute:=False)
    Ad2=Range(MyAdresscelluledépart).Offset(1,0).Address(RowAbsolute:=False)
    MaFormule="=ET(" & Ad1 & ">(AUJOURDHUI()+14);" & Ad1 & "<>""non réalisé"";" & Ad2 & "="""")"

    .FormatConditions.Add Type:=xlExpression, Formula1:= MaFormule
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With

End With

 

[MFuzier]

Merci beaucoup ! ton code fonctionne .... et je vais même pouvoir utiliser l'instruction Offset. Cela (la mise ne forme conditionnelle) va m'être fort utile car j'ai énormément de mise à jour conditionnelles, 4 règles par cellule sur 500 colonnes environ. J'ai dû finalement procéder par VBA pour les réinitialiser périodiquement de manière automatique : en effet, je ne comprends pas pourquoi les mises à jour conditionnelles, simples au départ (sur une colonne par exemple) "vivent leur vie" de manière autonome avec "auto création" de règles sur une ou plusieurs cellules de la colonne, ce qui rend la lecture des règles de maj conditionnelle illisibles. Pourriez-vous m'expliquer pourquoi ?

 

[patricktoulon]

Bonjour
c'est un peu flou
perso je pige pas le besoins de passer par vba pour rendre une MFC dynamique qui l'est déjà en natif

 

[MFuzier]

Bonjour,
Pour mettre une MFC dans une cellule qui nécessite 4 règles, il faut "écrire" manuellement dans Excel ces 4 règles. Lorsque ces règles se "détériorent" (ajout de nouvelles règles sans intervention de la part de la part de l'utilisateur), il faut les réinitialiser. Le fait de passer par VBA rend cette réinitialisation automatique au lieu de devoir les réécrire ou supprimer les règles "nouvelles" à la main. Mon interrogation est que je ne vois pas à quel moment de nouvelles règles s'additionnent sans intervention de ma part dans ces MFC

 

[patricktoulon]

re

Lorsque ces règles se "détériorent"

?????????????
il n'y a aucunes raisons que ça se détériore peut être n'a tu pas tout prévu dans celles ci
si vraiment ca se détériore c'est que

1. soit tu a un gros soucis avec ton app excel
2. soit tu t'amuse a déplacer les cellules et les mfc ne correspondent plus

c'est la qu'il faut régler le soucis
tu peux pas constamment mettre des plâtres sur une jambe de bois
tu t'en sortira jamais a chaque nouvelle modif tu va devoir non seulement changer la mfc mais aussi ton code vba qui te fait l'opération



en rajouter une par VBA ok mais la conditions est parametres tu les a bien fait(coder)dans VBA a la main que je sache
demain t'en veux une nouvelle ben tu refera la même opération

coder la formule ou paramétrer ta MFC dans la fenêtre appropriée ou dans VBA n'a ni queue ni sens là

c'est un peu réinventer la roue l'interface MFC tu l'a déjà

le serpent qui se mort la queue

 

[TooFatBoy]

J'ai dû finalement procéder par VBA pour les réinitialiser périodiquement de manière automatique : en effet, je ne comprends pas pourquoi les mises à jour conditionnelles, simples au départ (sur une colonne par exemple) "vivent leur vie" de manière autonome avec "auto création" de règles sur une ou plusieurs cellules de la colonne, ce qui rend la lecture des règles de maj conditionnelle illisibles. Pourriez-vous m'expliquer pourquoi ?

Ça vient en général de copier/coller multiples qui ajoutent chacun leur(s) MFC.

Je fais comme toi : je passe par du VBA pour supprimer toutes les MFC et pour ne remettre que les MFC nécessaires.

 

[MFuzier]

J'ai bien avancé avec ta réponse Marcel32. Merci beaucoup. J'essaie maintenant de "traduire" la règle suivante "=ET(OU($AV2>0;$AV2=""Non Réalisé"");$AV2<>""NA"";$AV2<>""Non suivi"";$AV2<>"""" )" en Formule comme tu me l'as conseillé, mais je bute sur une erreur de syntaxe. Voici ma "Traduction" :
MaFormule = "=ET(OU(" & Ad1 & "=""Non réalisé""; " & Ad1 & ">" & "0"" )"; " & Ad1 & "<>""NA""; & Ad1 & "<>""Non suivi"";" & Ad1 & "<>""""")" Où est l'erreur ? Est-ce dans le ET(OU ? Si je décompose la formule en deux sous formules concaténées, une pour le ET et une pour le OU, je n'ai plus d'erreur de compilation, mais le résultat est VRAI et non plus une chaine de caractères.

 

[TooFatBoy]

Voici ma "Traduction" :
MaFormule = "=ET(OU(" & Ad1 & "=""Non réalisé""; " & Ad1 & ">" & "0"" )"; " & Ad1 & "<>""NA""; & Ad1 & "<>""Non suivi"";" & Ad1 & "<>""""")"
Où est l'erreur ?

Il y a plusieurs erreurs.

J'ai souligné le premier groupe en violet.
1- Espace inutile.
2- " & " inutile.
3- "" inutile.
4- Espace inutile.
5- " inutile.
6- Espace inutile.
Donc au lieu de :
; " & Ad1 & ">" & "0"" )"; "
tu devrais avoir :
;" & Ad1 & ">0);"

La dernière erreur c'est un guillemet qui manque entre le ; et le & (juste après le NA).


Au départ tu as ça :
"=ET(OU($AV2=""Non Réalisé"";$AV2>0);$AV2<>""NA"";$AV2<>""Non suivi"";$AV2<>"""" )"
donc ça devrait donner ça :
"=ET(OU(" & Ad1 & "=""Non Réalisé"";" & Ad1 & ">0);" & Ad1 & "<>""NA"";" & Ad1 & "<>""Non suivi"";" & Ad1 & "<>"""" )"

 

[TooFatBoy]

Par contre, je reviens sur ce que disait PatrickToulon et sur ce que je disais aussi, au sujet des MFC qui se "transforment".

Pour que ce soit plus clair, on va partir d'une feuille qui ne comporte qu'une seule MFC s'appliquant sur une plage de cellules.
Les copier/coller peuvent ajouter des MFC. On se retrouve alors avec des MFC redondantes ou inutiles dont le temps de traitement peut devenir très long.
Comme je l'ai dit, j'utilise alors une macro pour supprimer les MFC et ne remettre en place que celle nécessaire.
Mais ce qu'il faut bien comprendre, c'est que dans le cas que j'évoque, la MFC se duplique lors des copier/coller, mais les MFC résultantes restent valables (car je les copie et colle dans la même colonne ou la même ligne et que les conditions de mes MFC sont prévues pour cela grâce à l'utilisation des $).
C'est juste la plage de cellules sur laquelle elle doit s'appliquer qui se démultiplie. Donc ma macro supprime toutes les MFC et ne remet que la MFC de départ, avec une seule plage de cellule à laquelle l'appliquer.

Donc, comme le dit PatrickToulon, si tes MFC en elles-même doivent être modifiées, c'est probablement parce que tu les recopies à des endroits où elles ne peuvent s'appliquer telles quelles (par exemple à cause d'un décalage de colonne).

Au départ, j'avais cru comprendre que c'était volontaire ce décalage (par exemple recopie à un endroit différent selon la feuille dans laquelle tu recopies cette MFC). Mais j'avoue que j'en suis moins certain maintenant...
Alors, comme il est de tradition ici : un petit fichier anonymisé serait peut-être le bienvenu.

 

[MFuzier]

Merci pour toutes ces informations et la qualité des réponses.
Déjà au niveau de la syntaxe, je viens de comprendre que le ";" fait partie des chaines de caractères à inclure et n'est pas dans ce cas considéré comme un séparateur. ça change tout !

Pour ce qui est des MFC, les 2 comportements que tu cites, Marcel32, sont utilisés :
1er comportement
en tant qu'administrateur de la base de données des tests (BDD), je fais des copier coller pour dupliquer avec les conditions prévues (avec les $) et en tenant compte des éventuels décalage de colonne.

2ème comportement
Cependant, les utilisateurs de la BDD ont accès, soit à des formulaires de saisie, ou bien vont directement dans la BDD et font des copier coller d'une colonne à l'autre (alors que les MFC sont applicables pour une colonne donnée !!), ce qui met le bazar.
D'où, la nécessité pour moi de supprimer les règles existantes et de regénérer périodiquement ces MFC relatives à des colonnes (d'où écriture de code en VBA).

Les colonnes qui comportent des MFC sont (pour la plupart) :
.soit des colonnes relatives à des dates prévisionnelles calculées (avec des couleurs indiquant si en retard par rapport au prévisionnel, à 7 jours de l'échéance, à 15 jours de l'échéance) Exemple Colonne "AT" Date de planification
. soit des colonnes relatives à des retards calculés Exemple Colonne "AV" Retard Planification
. soit des colonnes relatives à des pourcentages d'avancement de livrables. Exemple Colonne "AF" Profil du test défini

Normalement, ces colonnes ne devraient pas être modifiées par les utilisateurs (mais je n'ai pas mis de protection pour ces colonnes !)
Exemple de colonne avec bazar : la colonne BM . La MFC pour la barre de données est OK pour l'intervalle =$BM$2:$BM$108, mais il y a d'autres règles ajoutées =$BM$58, .....

Du reste, on peut voir toutes les règles avec "bazar" en recherchant les MFC non pas pour la colonne, mais pour la totalité de la feuille et les supprimer.

Tu pourras te rendre compte plus facilement avec le fichier joint.

Le fichier envoyé est la base brute. L'application développée consiste à visualiser cette base en fonction des besoins de suivi, d'avancement, de saisie, ......

 

[TooFatBoy]

2ème comportement
Cependant, les utilisateurs de la BDD ont accès, soit à des formulaires de saisie, ou bien vont directement dans la BDD et font des copier coller d'une colonne à l'autre (alors que les MFC sont applicables pour une colonne donnée !!), ce qui met le bazar.
D'où, la nécessité pour moi de supprimer les règles existantes et de regénérer périodiquement ces MFC relatives à des colonnes (d'où écriture de code en VBA).

Je crois qu'on a hélas tous connu cela...
Et si on met une protection sur la feuille, ça peut commencer à se compliquer.
Et si en plus on utilise le partage de fichier au niveau d'Excel, alors là c'est mort car impossible de déprotéger une feuille dans un fichier partagé, et impossible de modifier les MFC dans un fichier partagé...

Normalement, ces colonnes ne devraient pas être modifiées par les utilisateurs (mais je n'ai pas mis de protection pour ces colonnes !)
Exemple de colonne avec bazar : la colonne BM . La MFC pour la barre de données est OK pour l'intervalle =$BM$2:$BM$108, mais il y a d'autres règles ajoutées =$BM$58, .....

Du reste, on peut voir toutes les règles avec "bazar" en recherchant les MFC non pas pour la colonne, mais pour la totalité de la feuille et les supprimer.

Effectivement, c'est comme cela qu'on voit l'ensemble du bazar qui a été mis par les boulets qui utilisent le classeur...