XL 2010 Mise en forme conditionnel [VBA] critères multiple

[Nylream]

Bonjour,

J'aimerais utiliser le VBA pour ma mise en forme conditionnel (je ne souhaite pas utiliser l'outil de base).
J'ai trois type de mise en forme pour mon tableau :

1. Un mot à mettre en rouge si présent
2. Doublon sur une même colonne : fond de la ligne à changer (prioritaire)
   
3. le fond d'une ligne à changer, en fonction de deux cellule (F et G) sur cette même ligne

Concrètement:

• Si deux fois le même nom en colone A, le fond de la ligne en rouge
• Si (F= "Navy") + (G = "O-11", "O-10", "O-9") alors couleurs #xxxx
• Si (F= "Marines") + (G = "O-11", "O-10", "O-9") alors couleurs #yyyy
• le mot "Unkow" toujours en rouge

J'ai testé pour le mot "Unknow" en rouge, mais en vain, les autres étant plus complexe, je cale.

Sub ColorOnDouble()

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B2:$K="Unknow""
    ligne .Color = 255
   
End Sub

Merci d'avance pour votre aide,

 

[job75]

Re,

J'ai étudié vos noms définis PersFnAssign (colonne D) PersFnRole (colonne E) et PersFnRank (colonne H).

Il s'avère que les colonnes D:E ne sont modifiées que si l'on modifie la colonne A des noms.

Et en colonne H il n'y aura jamais de "Unknow".

Au post #11 je viens donc de simplifier la Worksheet_Change pour les polices.

Par ailleurs PersFnAssign et PersFnRole dépendent des noms se trouvant dans d'autres feuilles.

Pour que les polices en D:E soient mises à jour après modification de ces noms il faut ajouter ce code :

Private Sub Worksheet_Activate()
Worksheet_Change [A:A] 'lance la macro
End Sub

qui se déclenche quand on active la feuille.

A+

 

[Bebere]

Bonjour
après recherche,une mfc faites sous excel2010
je ne suis pas arrivé à colorer toute la ligne pour F:G

 

[Nylream]

Re Job75,

Je viens de tester et c'est en effet impeccable.

Merci pour les corrections !
_

Bonsoir Bebere,
Pas sur de comprendre ce que tu as fais, mais ce problème à été résolu par Job75 en vba.

 

[job75]

Re,

Juste un détail mais je suis sûr que vous l'aviez compris.

Pour mettre à jour le tableau si les couleurs ont été effacées, copiez-collez le tableau sur lui-même.

Bonne nuit.

 

[Nylream]

Merci Job75,
Bonne nuit également

 

[Nylream]

Bonjour Job75,

Merci pour toute ces corrections et améliorations.
J'ai appliqué les modifications que vous avez apporté, et j'ai également refait un fichier test avec l'ensemble des ajouts que j'ai apporté aux couleurs et aux différents rangs.
Certaines des couleurs étant sombre, j'ai également modifié la couleurs de police et ajouté les cadres.

Globalement ça fonctionne, mais j'ai évidement quelques effets de bords:

• La couleurs de police et les cadres sur la colonne des noms (A)
• La couleurs sur les "unknow" qui pour le moment, oblige à repasser sur chaque cellules.

Je ne vois pas d'autre soucis pour l'instant, et dans tout les cas, vous m'avez énormément aidé à avancer, merci beaucoup.
Je sais que vous avez déjà fait énormément, mais si jamais vous avez encore un peu de temps pour y jeter un œil, je vous remercie d'avance.

Nylream

 

[job75]

Bonjour Nylream, le forum,

Bravo vous avez bien avancé.

Votre fichier en retour, les codes ont été revus.

Pour la Worksheet_Change il est finalement beaucoup plus simple de recréer à chaque modification une MFC pour la coloration des "Unknow".

Votre macro Worksheet_SelectionChange n'allait pas du tout, je l'ai complètement changée.

A+

 

[Nylream]

Bonsoir Job75,

Je viens de récupérer et tester, c'est vraiment super.
J'ai encore pas mal de boulot sur ce fichier, mais il me manque des informations pour avancer, donc pour le moment, je crois qu'il est difficile de faire mieux

Un énorme merci à vous, pour votre aide et patience,

Nylream

 

[job75]

Bonjour Nylream, le forum,

Quelques améliorations dans ce fichier (2) :

- la macro Worksheet_Activate a été supprimée, elle est inutile puisqu'il y a maintenant une MFC

- la macro Recherche (doublons des feuilles Dispatch) a été revue, plus besoin d'une feuille auxiliaire.

La macro bien connue Quick sort est utilisée pour le tri de la liste.

Ne pas oublier Option Compare Text en haut du Module.

Bon week-end.

 

[Nylream]

Bonjour Job75,

Merci pour ces nouvelles améliorations, c'est vraiment soigné et loin de mes compétences.
Étant littéralement débutant (j'ai découvres le monde Excel avec ce fichier, avant ça, je n'allais pas plus loin que compléter une grille).

Je piétine sur deux autres points sur ce fichiers, et je me demandais si éventuellement, vous pourriez m'aider à résoudre ces problèmes.

Le premiers c'est la protection des cellules, ce fichier va être utiliser par d'autres personnes, et je crains qu'ils n'effacent par erreurs les formules. J'ai compris comment les verrouiller, mais du coup, lorsque le fichier est protégé, les VBA ne fonctionnnent pas correctement.
Dans ce post, on voit l'utilisation de :

Sheets(1). Unprotect Password:="***"
Sheets(1). Protect Password:="***"

Mais je n'arrive pas a comprendre comment l'utiliser (surtout où le placer)

Le second concerne la taille du tableau. Idéalement, j'aurais aimé que celui ci fasse exactement la taille "utilisé", et s’agrandisse à chaque ajout d'une ligne, que ce soit en fin de tableau ou par insértion. Mais mes essais se sont révélé pour le moins incertains, parfois ça semblait fonctionné, puis d'un coup, plus du tout sans que j'en comprenne la raison.
Du coup, j'ai commencé à entrer des valeurs en dur, mais je me retrouve au fil du temps avec des valeurs différentes à plein d'endroit ce qui est évidement mauvais sur le long terme.
J'ai une formule "Full"dans le gestionnaire de nom :

=DECALER(Members!$A$2;0;0;NBVAL(Members!$A:$A);NBVAL(Members!$1:$1))

Mais je n'ai pas trouvé comment l'appliquer en VBA, ni dans les MFC (pour le unknow en rouge)

Merci pour toute l'aide que vous m'avez déjà apporté,

Bon week-end,

 

[job75]

Re,

Pour protéger les feuilles "Dispatch" aucun problème :

Private Sub Workbook_Open()
Dim w As Worksheet
For Each w In Worksheets
  If w.Name Like "*_Dispatch" Then w.Protect "Nylream", UserInterfaceOnly:=True
Next
Rechercher
Saved = True 'évite l'invite à la fermeture si aucune modification
End Sub

Les macros peuvent s'exécuter malgré la protection grâce à UserInterfaceOnly:=True.

Par contre il ne faut pas protéger la feuille "Members" : on ne pourrait pas insérer ou supprimer des lignes puisqu'il faudrait verrouiller les colonnes D E H contenant des formules.

Pour à la fois créer et protéger les formules je propose au début de la Worksheet_Change :

Application.EnableEvents = False
'---évite l'insertion de lignes ou de colonnes avant H1---
If CStr([H1]) <> "RANK" Then Application.Undo: Application.EnableEvents = True: Exit Sub
'---bordures---
[A:K].Borders.LineStyle = xlNone 'RAZ
Intersect(UsedRange.EntireRow, [A:K]).Borders.Weight = xlThin
'---création et protection des formules en colonnes D E H---
Set P = Intersect(Rows("2:" & Rows.Count), Target.EntireRow, UsedRange.EntireRow)
If Not P Is Nothing Then
  For Each r In P.Rows
    If r.Cells(4).Formula <> "=PersFnAssign" Then r.Cells(4) = "=PersFnAssign"
    If r.Cells(5).Formula <> "=PersFnRole" Then r.Cells(5) = "=PersFnRole"
    If r.Cells(8).Formula <> "=PersFnRank" Then r.Cells(8) = "=PersFnRank"
  Next
End If
Application.EnableEvents = True

L'organisation en tableau Excel devient inutile, je l'ai supprimée.

Fichier (3).

Bonne nuit.

 

[Nylream]

Bonjour Job75, le forum,

Encore merci, j'ai du mal a réaliser non seulement le temps que j'aurais mis à essayer en vain sans votre aide.
Le fait qu'il n'y ai plus besoin de la mise en forme de tableau est vraiment pratique, ça exclue les fausse manip que je fais depuis le départ.

Dans ma macro de recherche au dessus, je défini un range précis de 250 ligne, si je crée une zone correspondante et dynamique que je nomme par exemple "Search", pourrais je mettre "=Search" pour que la formule de Tri s'adapte à la longueur du tableau ? (oui je sais c'est basique comme question)
Je ne suis pas certains de comprendre la différence entre tableau et champ, mais je n'ai pas de soucis avec les champs que j'ai crée jusqu'ici, je ne suis juste pas sur de comment les exploiter dans le VBA sans erreurs.

Encore merci à vous,

 

[job75]

Bonjour Nylream, le forum,

Je ne vois pas ce que viendrait faire une plage de 250 lignes puisque vous avez dit que le tableau pouvait comporter jusqu'à 600 lignes.

Ne créez pas des noms inutiles, il faut les limiter à ce qui est strictement nécessaire.

Bonne journée.

 

[job75]

Re,

Sur ce fichier (4) j'ai :

- déverrouillé toutes les cellules de la feuille "Members" et installé le filtre automatique

- modifié la macro :

Private Sub Workbook_Open()
Dim w As Worksheet
Feuil2.Protect "Nylream", UserInterfaceOnly:=True, _
  AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, _
  AllowSorting:=True, AllowFiltering:=True
For Each w In Worksheets
  If w.Name Like "*_Dispatch" Then w.Protect "Nylream", UserInterfaceOnly:=True
Next
Rechercher
Saved = True 'évite l'invite à la fermeture si aucune modification
End Sub

- fermé et rouvert le fichier.

Cela empêche de modifier les noms définis et de faire n'importe quoi sur la feuille.

A+

 

[Nylream]

Re,

J'ai corriger la macro de TRI, c'est plus cohérent je crois.
Merci pour les récentes modifications, je les ai intégré et ai fait quelques modifs pour la suite de ce que j'ai a faire.

En tout cas, c'est vraiment étonnant ce qu'il est faisable de faire lorsqu'on a les connaissances !

Merci encore à vous,

[Edit]
Je me demande si il est possible de conserver une donnée qui a changé a cause d'une macro, en mémoire ?
Exemple :
baptiste Smith est Alpha Fligh Leader dans le 1st Squadron, et doit être déplacé comme XO dans le 2nd Squadron.
Est il possible d'avoir les anciennes valeurs Role et Assignment en mémoire, et pouvoir les ressortir avec une formule ?
Si ce n'est pas faisable, je comprendrais, je ne sais pas si Excel peut conserver des données cachées

Merci d'avance,