Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

[jp65]

Bonjour le forum

Je vous soumet ce sujet qui me pose problème.

Je voudrais colorer les cellules d'une ligne qui contiennent la valeur la plus élevée et la plus petite.
Les cellules concernées ne sont pas contigues mais liées à l'entête de leur colonne.
Je joint un fichier pour tenter d'être plus clair.

Par contre je souhaiterais que celà soit fait en VBA et non en MFC.😛

Je vous remercie pour l'aide que vous voudrez bien m'apporter.

Bonne soirée

 

[jpb388]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonsoir à tous
regarde si cela te va
a+
jp

 

[jp65]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour jpb388
merci de ta réponse, je teste dès que possible et je te tiens au courant.
Bonne journée

 

[Modeste]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour,

Une proposition de simplification du code proposé par jpb388 ... Pas une critique de la proposition, juste une façon plus "concise" (qui est sans aucun doute encore perfectible ... après tout, nous sommes sur un forum d'entraide 🙂).

Cette version colore plusieurs cellules si, dans la ligne, il y a des ex-aequos pour le Min ou le Max.

Private Sub Worksheet_Change(ByVal Target As Range)
    dercol = Cells(3, Columns.Count).End(xlToLeft).Column 'repérer la dernière colonne utilisée en ligne 3
    Cells(Target.Row, 5).Resize(1, dercol - 4).Interior.ColorIndex = xlNone 'mise "à blanc"
    On Error GoTo fin 'si aucune constante dans la ligne
    Set pl = Cells(Target.Row, 5).Resize(1, dercol - 4).SpecialCells(xlCellTypeConstants)
    Min = Application.Min(pl)
    Max = Application.Max(pl)
    For Each c In pl
        If c = Min Then c.Interior.ColorIndex = 43 'vert
        If c = Max Then c.Interior.ColorIndex = 3 'rouge
    Next c
fin:
End Sub

 

[jpb388]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour Modeste
oui en effet court et efficace
Cordialement
jp

 

[jp65]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour le forum
Bonjour jpb388 et Modeste

J'ai enfin pu tester vos deux codes.
Il fonctionnent presque parfaitement.

Explication du "presque":😕

Je n'ai pas formulé assez clairement ma demande initiale. Il faut que le code recherche les min et max dans les cellules de chaque lignes mais suivant les colonnes qui ont le même entête.
Par exemple pour une ligne faire la recherche min max dans toute les cellules dont l'entête de colonne est A.
Faire de même pour les cellules avec entête de colonne B, etc.. jusqu'à D dans mon classeur exemple.
Je joins un nouveau fichier modèle, espérant avoir été plus explicite.🙂

Merci pour votre assistance.

 

[jp65]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour le forum

J'ai travaillé sur le deuxième code fourni.
Pardon Modeste, tu as pris la peine de simplifier le code à jpb388 et moi je viens de le faire grossir encore plus.
J'arrive à isoler le traitement des cellules sur celles qui ont leur entête de colonne en commun.
Voici le code modifié:

Private Sub Worksheet_Change(ByVal Target As Range)

'repérer la dernière colonne utilisée en ligne 3
    dercol = Cells(3, Columns.Count).End(xlToLeft).Column
'si aucune constante dans la ligne
   On Error GoTo fin
   Set pl1 = Cells(Target.Row, 5).Resize(1, dercol - 1) '.SpecialCells(xlCellTypeConstants)
   Set pl2 = Cells(Target.Row, 5).Resize(1, dercol - 2)
   Set pl3 = Cells(Target.Row, 5).Resize(1, dercol - 3)
   Set pl4 = Cells(Target.Row, 5).Resize(1, dercol - 4)
    Min1 = Application.Min(pl1)
    Max1 = Application.Max(pl1)
    Min2 = Application.Min(pl2)
    Max2 = Application.Max(pl2)
    Min3 = Application.Min(pl3)
    Max3 = Application.Max(pl3)
    Min4 = Application.Min(pl4)
    Max4 = Application.Max(pl4)
    For Each c In pl1
       If c = Min1 Then c.Interior.ColorIndex = 43 'vert
       If c = Max1 Then c.Interior.ColorIndex = 3 'rouge
       If c = "" Then c.Interior.ColorIndex = 2
       If c < Max1 Then c.Interior.ColorIndex = 2
   Next c
   For Each c In pl2
       If c = Min2 Then c.Interior.ColorIndex = 43 'vert
       If c = Max2 Then c.Interior.ColorIndex = 3 'rouge
       If c = "" Then c.Interior.ColorIndex = 2
   Next c
   For Each c In pl3
       If c = Min3 Then c.Interior.ColorIndex = 43 'vert
       If c = Max3 Then c.Interior.ColorIndex = 3 'rouge
       If c = "" Then c.Interior.ColorIndex = 2
   Next c
   For Each c In pl4
       If c = Min4 Then c.Interior.ColorIndex = 43 'vert
       If c = Max4 Then c.Interior.ColorIndex = 3 'rouge
       If c = "" Then c.Interior.ColorIndex = 2
   Next c
fin:
End Sub

Avant de classer le post comme résolu, y a t'il un moyen d'alléger un peu ce dernier code.

Merci encore

Bonne journée

 

[Staple1600]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour à tous

Sur la base de code de Modeste (que je salue au passage)
(et si j'ai bien compris la question)

Sub e()
Dim nbc&, c, p As Range, i As Byte
nbc = ActiveSheet.UsedRange.Columns.Count
For i = 1 To nbc
On Error Resume Next
Set p = Columns(i).SpecialCells(2, 1)
For Each c In p
If c = Application.Max(p) Then c.Interior.ColorIndex = 43
If c = Application.Min(p) Then c.Interior.ColorIndex = 3
Next
Next
End Sub

 

[Modeste]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour jp65, le fil,
Salut JM (que j'aperçois dans le ciel qui poudroie!) 🙂

@jp65: avant de déterminer si le code peut être simplifié, il faudrait vérifier s'il fait ce que tu attends!?

Après tes 4 "Set pl[SUB]x[/SUB] ..." ajoute pl1.Select, puis pl2.Select, etc et exécute ton code en pas-à-pas. Pour ces 4 nouvelles instructions, regarde à chaque fois, dans ta feuille, la plage qui est sélectionnée ...
Je ne comprenais pas ton histoire de colonnes à en-têtes identiques, mais c'est encore moins le cas, maintenant 😕

Ne prendrais-tu pas la peine d'expliquer, en gardant en mémoire que nous ne connaissons pas ton objectif, ton contexte de travail avec ce fichier, ni ce qui te semble évident ... à toi!?

 

[jp65]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour Modeste, Staple1600

Mes explications et fichiers joints n'ont visiblement pas été convaincants.
Je tente un troisième modèle qui sera peux-être plus visuel.
Je suis conscient de la gêne que je génère mais malheureusement je suis autant novice en vba qu'en explications efficaces.

Merci pour votre attention si patiente.

 

[Staple1600]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Re

Remarques
Normalement dans un tableau logiquement agencé, les entêtes sont uniques
(Il ne devrait pas y avoir deux Entêtes 1)

Tu cherches bien à indentifier les MAX et MIN sur chaque ligne non ?

Plus ca va, moins je comprends 😉

Heureusement qu'on est plusieurs dans fils, on finira bien par comprendre où il faut en venir 😉

 

[jpb388]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Bonjour à tous,Staple1600,Modeste,jp65
Teste et dis moi

Modeste j'ai bien compris ta simplification par ligne et te remercie mais je n'est pas réussi à appliquer ta méthode dans le cas présent.
Peux tu éclairer ma lanterne ou c'est peut être pas possible

a+
jp

 

[jp65]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Re à tous

Allors, dans l'ordre.

Modeste
J'ai fais ce que tu m'a indiqué est effectivement le code ne réagit pas comme je l'espérait.
En fait il "semblait" fonctionner correctement mais ce n'est pas le cas.
Leçon 1: patienter un peu avant de balancer un code sur le forum en le qualifiant comme fonctionnel alors que ce n'est pas tout à fait le cas. 😡

Staple1600
Oui on peut rencontrer plusieurs fois le même entête dans un tableau.
En l’occurrence les colonnes et lignes contenant des "*" et qui ne sont pas concernées par le code sont en fait dans la vrai vie des références diverses de matériels. Les groupes de colonnes A-B-C-D que l'on peut retrouver x fois correspondent pour chaque groupe à des fournisseurs divers (chaque fournisseur a son groupe A-B-C-D).
A= prix
B=délai
C=minimum cde
D=Réf. fournisseur
Comme il y a plusieurs fournisseurs, il y a forcément plusieurs fois les mêmes entêtes.
Leçon 2: donner plus de détails qui correspondent à la vrai vie du tableau. 😡

jpb388
Ton dernier code fonctionne (Aie j'ai déjà oublié la leçon 1 😛).
Juste un petit bémol (ouf) lorsque deux cellules on la même valeur il n'y en a qu'une qui est colorée.
Pour en revenir à la vraie vie du tableau il peut y avoir des valeurs en double, triple, plus encore... et donc il faut que tous ces "doublons" soient colorés en vert ou rouge selon le Min Max.
Leçon 3: pour l'instant rien mais ça ne saurait tarder.

Encore merci à vous tous en espérant que vous ne trouverez pas ces commentaires trop ridicules.

 

[Modeste]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

Re à tous,

en espérant que vous ne trouverez pas ces commentaires trop ridicules.

Au moins, ils montrent que tu es attentif, que tu acceptes de te remettre en cause ... et que tu as le sens de l'humour ... denrées qui se font rare dans notre bac à sable préféré!

@jpb388: tu aurais tort de trop vouloir t'inspirer de mes essais parfois laborieux 😱

Avec ce que j'ai compris, cette fois, un autre essai:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 5 Or Target.Row < 4 Then Exit Sub
If Target.Count > 1 Then Exit Sub

Col = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
colModif = (Target.Column - 5) Mod 4
Set pl = Target
For x = 5 To Col Step 4
    Set pl = Union(pl, Cells(Target.Row, x).Offset(0, colModif))
    Cells(Target.Row, x).Offset(0, colModif).Interior.ColorIndex = xlNone
Next x
Min = Application.Min(pl)
Max = Application.Max(pl)
For x = 5 To Col Step 4
    If Cells(Target.Row, x).Offset(0, colModif) = Min Then Cells(Target.Row, x).Offset(0, colModif).Interior.ColorIndex = 43
    If Cells(Target.Row, x).Offset(0, colModif) = Max Then Cells(Target.Row, x).Offset(0, colModif).Interior.ColorIndex = 3
Next x
End Sub

Je commenterai si besoin ... et surtout si le résultat est concluant (après une batterie de tests aussi complète que possible 😉).
Là, je vais voir si je peux passer un peu de temps à travailler au jardin

 

[jpb388]

Re : Recherche valeur MIN MAX dans lignes suivant l'entête des colonnes

re
ma version

oui de trop peut être mais c'est quand même en s'inspirant des meilleurs que l'on progresse ce qui ne veux pas dire que je ferai absolument pareil
pour étoffer mon propos il n'y a qu'a regarder la longueur de ta macro et la mienne pour faire la même chose
comme on dit il n'y a pas photo