XL 2016 Mise en forme conditionnelle en vert de noms, si ces noms figurent dans une liste de noms d'un autre onglet (formules dans MFormCond, ou VBA)

[JaGAF]

Bonjour,

DE L’AIDE, J’AI BESOIN DE QUELQU’UN



Je souhaite appliquée une mise en forme conditionnelle, à une colonne de noms,

en fonction de si ces noms figurent (nom exact, partiel ou inexistant)

dans une liste de référence. J’utilise Excel 2016.



Pour cela, dans un même fichier j’ai deux onglets.

* Un onglet nommé « Ptf ».

Dans la colonne B, il y a la liste noms de 30 à 50 clients suivis.

J’ai nommé la liste de clients « CLIENT_NAME », dans cet onglet « Ptf ».



* L’autre onglet (au nom variable d’une extraction à l’autre),

En colonne F aussi nommée CLIENT_NAME, figure une liste de tous les clients connus.

Cela représente de 10 000 à 45 000 lignes.


>>> Je souhaite que :
* si la cellule de la colonne F, contient EXACTEMENT l’un des noms de la liste CLIENT_NAME ,

alors que la ligne soit verte,
* si la cellule de la colonne F, contient partiellement l’un des noms de la liste CLIENT_NAME ,

alors que la ligne soit jaune,
* si la cellule de la colonne F, NE contient PAS l’un des noms de la liste CLIENT_NAME ,

alors que la ligne soit rouge.



?? Y a t-il un solution, pour une réponse rapide (sans planter Excel) :

* via la mise en forme conditionnelle avec la condition qui pointe vers la liste de clients de l’onglet Ptf ? Si oui, comment ?

* via une formule SI... RechercheV . Si oui, quelle formule ?

* via une macro VBA ? Si oui, comment s’écrirait le code ?

Après 10 heures de tests et de plantages Excel, force est de constater que je ne m’en sorts pas, ni avec les formules Excel, ni avec la mise en forme conditionnelle !

Si vous pouvez m’aider je suis preneuse,

et par avance, je vous en remercie grandement.
Alilu

 

[sylvanu]

Bonjour Jagaf, et bienvenu sur XLD,
Prenez l'habitude de joindre un petit fichier test.
Cela permet de mieux cerner le problème, d'être au plus proche de votre besoin ... et d'éviter de se le retaper.
En PJ un essai avec ce que j'ai compris, par MFC avec :

Existe              =NB.SI(Liste;A1)+NB.SI(Liste;"*"&A1&"*")=2
Existe en partie    =NB.SI(Liste;A1)+NB.SI(Liste;"*"&A1&"*")=1
N'existe pas        =NB.SI(Liste;A1)+NB.SI(Liste;"*"&A1&"*")=0

A noter que "Existe en partie" peut conduire à des ambiguïtés comme "ON" qui pourrait être dans SIMON ou DUPONT.

 

[JaGAF]

Bonjour Sylvanu,

Tout d'abord, un grand merci d'avoir pris du temps pour réfléchir à une solution.
De plus, je prend note du support test xls nécessaire (joint dessous).
Enfin, je vais tester votre code immédiatement et reviens vers vous
JaGAF

 

[sylvanu]

Bonsoir JaGaf, Bruno,
Désolé, ça je ne sais pas faire.
Je sais faire comme dans votre demande initiale, et comme dans ma PJ :

si la cellule de la colonne F, contient partiellement l’un des noms de la liste CLIENT_NAME ,

Alors que là, votre fichier demande exactement le contraire.

si l’un des noms de la liste CLIENT_NAME contient partiellement la cellule de la colonne F

Par ex "Black Swing" est un morceau de "BLACK SWING (laundry)" de la liste supplier, mais "ACCS CORPORATE CATERING" n'est pas un morceau dans la liste, c'est le contraire c'est "ACCS" qui est compris dans "ACCS CORPORATE CATERING".

Je ne sais pas si c'est faisable en formules, peut être en VBA.
D'où l'utilité d'un fichier test, et surtout d'une demande correctement exprimée.

 

[JaGAF]

ça y est, cela fonctionne !
Donc :
> étape 1 : nommer la liste de référence des noms "Liste"
aller dans l'onglet Excel : Formules
sélectionner l'icône : Définir un nom
nommer "Liste"
NE PAS préciser de zone, laisser Classeur
préciser la plage concernée ("fait référence à")

> étape 2 : sélectionner la colonne à "colorer"
aller dans : Mise en Forme Conditionnelle (MFC)
Nouvelle Règle
Utiliser une formules pour déterminer .....
Positionner la formule : =NB.SI(Liste;A1)+NB.SI(Liste;"*"&A1&"*")=2
Format : mettre en vert

> étape 3 : renouveler l'étape 2 avec la formule : =NB.SI(Liste;A1)+NB.SI(Liste;"*"&A1&"*")=1
Format : mettre en jaune

> étape 4 =NB.SI(Liste;A1)+NB.SI(Liste;"*"&A1&"*")=0
Format : mettre en rouge
Appliquer, ok

C'est bon ! Un grand MERCI Sylvanu.

Maintenant je dois trouver comment faire en VBA,
car je vais devoir reproduire cela près de 200 fois.

 

[sylvanu]

Je ne comprends pas.
La zone "à colorer" est bien feuille Testeur colonne F, et la liste est bien "DataExport_2022-10-06_13-57-27" colonne F ?
Si oui, je ne comprends pas comment ça peut fonctionner.