Mise en forme conditionnelle via VBA

Bonjour,

J'ai créé un fichier permettant à mon équipe de suivre les commandes de nos clients, et notamment leurs expéditions. Actuellement, deux mises en forme, créées classiquement depuis l'outil "Mise en forme conditionnelle", sont dédiées à une colonne nommée "butée d'expédition". Il s'agit là de la date limite où nos opérateurs doivent expédier la commande selon le type de livraison choisi par le client (24h, 72h et 96h).

Mon problème, mes mises en formes "sautent" régulièrement lorsque mes collaborateurs utilisent le fichier, notamment lorsqu'une ligne est supprimée ou le fichier récupéré suite à une mauvaise manipulation.

C'est pourquoi j'aimerais que deux mises en forme soient gérées directement depuis VBA.

Dans mon fichier (fichier test ci-joint), ces trois colonnes sont vérifiées pour appliquer la bonne mise en forme.

190809061456823380

4 cas de figures peuvent se présenter :

1) La cellule de la "butée expédition" est vide => fond gris par défaut

2) La cellule de la "butée expédition" est une date supérieure à la date actuelle et la cellule "butée respectée" est vide => fond vert

Cette condition est vérifiée par cette formule :

=ET(OU($K3="OUI";Réglages!$D$4<$J3);$J3<>"")

3) La cellule de la "butée expédition" est une date inférieure à la date actuelle et la cellule "butée respectée" est OUI => fond vert

Cette condition est vérifiée par cette formule (indique que le délai a été respecté) :

=ET(OU($K3="OUI";Réglages!$D$4<$J3);$J3<>"")

4) La cellule de la "butée expédition" est une date inférieure à la date actuelle et la cellule "butée respectée" est NON => fond rouge

Cette condition est vérifiée par cette formule (indique que nous n'avons pas respecté le délai) :

=ET(OU(ET($K$8="";Réglages!$D$4>$J9);$K9="NON");$I9<>"")

Malheureusement je ne sais pas trop comment faire pour transposer ces formule en un code VBA qui me donnerait les mêmes mises en formes. Vous remarquerez dans le fichier, des cellules au texte vert sous fond gris, c'est précisément dans ces cellules que la mise en forme conditionnelle a sauté (notamment lorsque les autres cellules de la même ligne ont été modifiées à posteriori).

Merci d'avance pour votre aide,

Baptiste

21suivi-cmde.xlsm (31.39 Ko)

Bonsoir,

une des solutions que j'ai mis en application sur une (ou plusieurs) de mes applications est, s'il n'y a pas beaucoup de MFC sur la feuille, La suppression des trois MFC conditionnelles existantes ce qui a pour but d'effacer les "erreurs" dues aux différentes manipulation, puis de les recréer pour mettre à jour la feuille avec la prise en compte "correcte" de la MFC.

Ci joint un travail que j'ai fait il y a quelque temps :

41mfc-par-vba.xlsm (46.05 Ko)

Il est vrai qu'il faudrait que je le "nettoie" et le réactualise afin de le rendre plus accessible au niveau de la compréhension, mais au niveau du code cela permet de gérer et de créer toutes sortes de MFC par VBA et ceci quelque soit le remplissage des cellules...

N'ayant rien trouver sur ce sujet, j'ai fais des tests et maintenant le code prend en compte la mise en place des dégradés !

Regardez, cela vous mettra certainement sur "la bonne voix".

@ bientôt

LouReeD

Bonsoir,

Merci de m'avoir répondu. J'avoue être un peu perdu dans le codage VBA de la feuille de calcul.

En gros, de quelle façon vais-je pouvoir intégrer mes formules qui vérifient les conditions d'application des MFC directement dans le codage VBA.

Merci d'avance. Baptiste.

Bonsoir horusbk, LouReed , le forum,

Un essai.....

Sub testMFC()

Dim i As Long, dl As Long

   Application.ScreenUpdating = False

 With Sheets("Suivi des commandes")
  dl = .Range("A" & Rows.Count).End(xlUp).Row
   For i = 3 To dl
    If .Range("J" & i) = "" Then
       .Range("J" & i).Interior.ColorIndex = 15                         'si délai expédition vide : gris (CAS 1)
     ElseIf .Range("J" & i) > Date And .Range("K" & i) = "" Then
        .Range("J" & i).Interior.ColorIndex = 4                         'si butée expédition > aujourd'hui et délai respecté est vide : vert (CAS 2)
     ElseIf .Range("J" & i) < Date And .Range("K" & i) = "OUI" Then
        .Range("J" & i).Interior.ColorIndex = 4                         'si butée expédition < aujourd'hui et délai respecté =OUI : vert  (CAS 3)
    ElseIf .Range("J" & i) < Date And .Range("K" & i) = "NON" Then
       .Range("J" & i).Interior.ColorIndex = 3                          'si butée expédition < aujourd'hui et délai respecté =NON : rouge (CAS 4)
    Else
       .Range("J" & i).Interior.ColorIndex = xlNone                     'aucune couleur pour les autres conditions
    End If
   Next i
 End With

   Application.ScreenUpdating = True
End Sub
21test-mfc-vba.xlsm (33.06 Ko)

Bonne soirée,

Bonsoir xorsankukai,

Merci pour ta réponse, c'est exactement ce dont j'avais besoin

Toutefois, y a-t-il un moyen d'appliquer la MFC à toutes les données qui sont dans le fichier (et non les nouvelles qui seront ajoutées).

Lorsque j'ai testé dans mon "vrai" fichier, en ajoutant une nouvelle ligne, certaines de mes mises en formes ont sautées et ton codage a été appliqués à de façon éparse à 2/3 cellules.

edit: De plus, pourquoi la commande d'appel de la fonction testMFC se situe dans une private sub et n'est pas à part ?

En te remerciant par avance,

Baptiste

Bonsoir,

pour continuer dans ma lancée sans pour autant dénigrer ce qu'à fait XOR (comme je le dis toujours, j'ai fais mon choix entre les deux ! )

Je disais donc voici votre fichier modifié avec ma première attention, c'est à dire de mettre en place une MFC qui est régénérée à chaque modification de la feuille :

Le principe ? Il vous suffit d'aller sur la feuille "Réglages" et dans les trois cellules de la colonne I inscrire les formules des MFC, et dans les cellules de la colonne K mettre la MFC voulue pour une formule renvoyant "VRAI".

Maintenant vous pouvez ajoutez une ligne, la modifier ou bien la supprimer, les MFC sera régénérée sur la taille réel du tableau.

Ce code VBA supporte les dégradés.

Vous pouvez également ajouter de nouvelles formules et de nouvelles MFC sur la feuille "Réglages" mais le code ne gère que la colonne J de la première feuille, et il supprime toutes les MFC présente sur cette feuille avant de régénérer celles "programmées".

Donc cette solution est faite pour un classeur "stable" dans le temps !

La MFC est régénérée également lors de l'activation de la feuille, comme ceci si vous modifier une des cellules "sources" de MFC en revenant sur la première feuille c'est mis à jour...

@ bientôt

LouReeD

Bonjour horusbk, LouReed, le forum,

@horusbk:

  • Comme j'utilise une boucle, le traitement s'effectue ligne par ligne.
  • Comme tu utilisais l'évènement Change de la feuille, j'en ai profité pour inclure l'appel de la macro pour qu'elle se lance à chaque modif.sur la feuille.

Je comprends que cela ne réponde pas à ton attente, , la solution par MFC est bien meilleure, teste la version de LouReed, il n'y a pas photo.

@LouReed:

  • J'accepte toutes critiques (bonnes où mauvaises),du moment qu'elles me permettent de progresser,
  • Après avoir testé ta proposition, je constate qu'il me reste encore beaucoup à apprendre,
    Chapeau!

En essayant de travaillé avec les MFC, j'en suis resté là :

Sub testMFC()
 Dim i As Long, dl As Long
 Dim plage As Range

   Application.ScreenUpdating = False

    With Sheets("Suivi des commandes")
      dl = .Range("A" & Rows.Count).End(xlUp).Row
      Set plage = .Range("J3:J" & dl)
       With plage
        .FormatConditions.Delete
        .FormatConditions.Add(xlExpression, , "=ET($I3="""";$J3="""")").Interior _
         .Color = RGB(224, 224, 224)   'CAS 1 : GRIS
        .FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4<$J3;$K3="""")").Interior _
         .Color = RGB(0, 255, 0)       'CAS 2 : VERT
        .FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4>$J3;$K3=""OUI"")").Interior _
         .Color = RGB(0, 255, 0)       'CAS 3 : VERT
        .FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4>$J3;$K3=""NON"")").Interior _
         .Color = RGB(255, 0, 0)       'CAS 4 : ROUGE
        .FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4>$J3;$K3="""")").Interior _
         .Color = RGB(224, 224, 224)   'CAS autre : Gris  (si < et rien)
        .FormatConditions.Add(xlExpression, , "=ET(Réglages!$D$4<$J3;$K3=""NON"")").Interior _
         .Color = RGB(255, 0, 0)       'CAS autre : ROUGE (si > et NON)
       End With
    End With

    Application.ScreenUpdating = True
End Sub

Mais je me suis un peu perdu....

11test-mfc-vba.xlsm (33.90 Ko)

Amitiés,

Bonjour,

l'inconvénient de "mon système" c'est le fait de devoir créer une nouvelle "feuille" avec les MFC, mais l'avantage, pour les "non initiés" ou utilisateurs "novice", il est simple de modifier une MFC, on sélectionne la cellule de la condition est on lui donne la forme que l'on veut.

Le seul truc c'est que l'épaisseur des bordures ne sont pas prise en compte, ni la taille de la police.

Le deuxième avantage c'est qu'on fait référence à une cellule et à ses MF du coup nul besoin de connaître les valeur numérique ou expression de chacune de ces mises en forme, ce ne sont que des "copier/coller" de données.

•J'accepte toutes critiques (bonnes où mauvaises),du moment qu'elles me permettent de progresser

Attention ! pas de méprise, il n'y avait aucune intention de critiquer quoi que ce soit, c'est juste que certain intervenant "n'accepte pas" que l'on puisse intervenir et prendre un autre chemin qu'eux sur la réponse d'un fil...

Et pour ce qui vous reste à apprendre ce n'est pas moi qui vais vous dire ce qu'il reste... Il m'en reste tellement pour moi !

@ bientôt

LouReeD

Bonsoir,

pour faire "plus propre" dans la "Sub" Change de la feuille, il serait visuellement plus confortable de rajouter ce code à la place de l'appel simple "Call MFC" :

    Application.ScreenUpdating = False
    Call MFC
    Target.Activate

et pour la "sub" Activate :

    Application.ScreenUpdating = False
    Call MFC
    Range("A1").Activate

voir le résultat sur le fichier joint :

@ bientôt

LouReeD

Bonjour vous deux

Merci beaucoup pour vos réponses ! Je vous répond un peu en retard, j'attendais d'être au boulot pour pouvoir tester la programmation de LouReeD.

J'aurais souhaité savoir s'il était nécessaire de supprimer mes anciennes mises en formes conditionnelles ou, s'il fallait les recréer dans la feuille réglage ?

Je vous remercie pour votre aide,

Je vais tester le code sur mon fichier officiel durant le weekend pour voir si d'éventuels bugs apparaissent. Je vous tiens au courant.

Merci encore !

Baptiste

Bonsoir,

je parle pour mon code :

Toutes les MFC de la feuille vont être supprimées, et seulement les trois de la feuilles réglages vont être régénérées à chaque activation de la feuille ou lors d'un changement sur cette dernière.

Donc s'il y a d'ancienne MFC, elles seront supprimées, donc pas besoin de les enlever.

Il ne faut pas oublier de faire une copie de sauvegarde avant toutes manipulation de votre fichier !

@ bientôt

LouReeD

Bonsoir,

Effectivement c'est pour cela que je me le suis envoyé pour le tester au calme, chez moi.

Bonne soirée et bon weekend,

Baptiste

Bonjour,

(J'ai pas réussi à éditer mon message précédent désolé )

J'ai donc testé le codage qui fonctionne très bien et qui répond parfaitement à mes attentes ! Merci encore de m'avoir aidé

J'ai toutefois un léger bug : lorsque je remplis le fichier et que j'appuis sur "entrer" pour valider la saisie dans une cellule, l'encadré vert passe sur la cellule du dessous (ou de gauche si j'utilise les flèches directionnelles) et tout de suite après repasse dans la cellule précédemment modifié.

J'ai fait plusieurs fois le test avec et sans le nouveau codage des MFC et le problème est présent uniquement avec le codage.

Bon weekend ^^

Baptiste

Bonjour,

c'est du à la ligne "Target.activate", alors vous pouvez l'enlever.

@ bientôt

LouReeD

Bon et bien voilà,

Dans le code précédent, pour régénérer les MFC, je sélectionnais la plage de cellule de la colonne J, Erreur !

Du coup pour dé sélectionner cette plage j'avais ajouté "Target.Activate" ce qui faisait que la cellule active "revenait" sur la cellule modifiée... Pas top comme vous l'avez dis, enfin vous avez utilisé le terme de bug

Voici le fichier joint modifié :

Sur celui ci je ne sélectionne plus la plage de la colonne J, je l'identifie simplement, comme cela devrait être le plus souvent (voir tout le temps) dans les codes VBA, du coup il n'y a pas de "dé sélection" à faire, du coup que ce soit avec [Entrée] ou [Flèche] la nouvelle cellule reste sélectionnée !

@ bientôt

LouReeD

Bonjour LouReeD,

Merci de tes explications et pour cette mise à jour

Je teste ça de suite sur mon fichier.

@ +

Baptiste.

Bonjour,

j'attends donc le résultat !

@ bientôt

LouReeD

Bonjour LouReeD,

Essais concluants ça fonctionne parfaitement merci encore pour ton aide !

Je vais pouvoir mettre à jour le fichier lundi matin et normalement... plus de soucis avec des MFC qui disparaissent

Bonne fin de weekend !

Baptiste.

Rechercher des sujets similaires à "mise forme conditionnelle via vba"