Discussion :

[moh2ss]

Bonjour à tous,

Je suis débutant dans ce forum et dans le domaine.
Je souhaiterai créer une macro avec l'objectif suivant:

- Sur une 6 cellules consécutives et à chaque changement de cellule, je teste l’existence du contenu saisi sur une liste de données en feuille2 (fraise,banane,poire,pomme,prune,abricot,cerise,raisin,melon,framboise)

- Si le contenu existe: la cellule se colore en vert

- Si le contenu n'existe pas: la cellule se colore en rouge

Deux options: soit je créé un bouton "check", soit ça se fait à chaque changement de cellule (select_change).

Merci pour votre aide!

[Philippe Tulliez]

Bonjour,
3ème option : N'est-ce pas plus simple de passer par l'outil de mise en forme conditionnelle ?

[moh2ss]

Bonjour,

Merci pour votre réponse.
Si cela suffit, je suis preneur.Je n'arrive pas à trouver la bonne méthode, auriez-vous des indications?

Merci!

[Philippe Tulliez]

Bonjour,
Imaginons que les références de produit se trouve en colonne A à partir de la ligne 2 et la liste des produits en feuille nommée Feuil2 sur la plage A2:A10
Sélectionner la plage où doit avoir lieu la mise en forme conditionnelle (par exemple $A$2:$A$50)
Sélectionner la commande Gérer les règles... de l'outil Mise en forme conditionnelle (onglet [Accueil], groupe Style)
Dans la boîte de dialogue Gestionnaire des règles de la mise en forme conditionnelle, sélectionner Nouvelle règle... et sélectionner comme type de règle Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
Ensuite insérer la formule (La formule doit toujours renvoyer VRAI ou FAUX)
Pour appliquer un format en cas d'inexistence de la valeur de la cellule se trouvant en colonne A, la formule est

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(NBCAR($A2);ESTNA(EQUIV($A2;Feuil2!$A$2:$A$10;0)))

Pour appliquer un format si la valeur se retrouve dans la liste

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV($A2;Feuil2!$A$2:$A$6;0)

Idéalement, il faudrait nommer la plage où se trouve la liste des produits (A2:A10). L'exemple montré plus haut ne fonctionnant que sur las versions 2010 et supérieures.
Pour les versions antérieures, l'outil Mise en forme conditionnelle ne pouvait faire référence à une plage se trouvant sur une autre feuille.
Il y aurait donc lieu de modifier alors la référence à Feuil2!$A$2:$A$10 par le nom donné à cette plage.
Soit pour le premier exemple en imaginant que le nom donné serait ListProduit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(NBCAR($A2);ESTNA(EQUIV($A2;ListProduit;0)))

Pour rappel : Pour nommer une cellule ou une plage de cellule Onglet [Formules], groupe Nom définis, commande Gestionnaire de noms

[moh2ss]

Merci Beaucoup pour les explications claires et précises!

J'ai bien suivi votre méthode. Deux problèmes se posent:

- La condition "si non existe" annule la condition "si existe" alors que si je mets l'une ou l'autre seulement, ça fonctionne

- Une fois la plage renommée et intégrée dans la formule, ça ne fonctionne pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(NBCAR(F$11);ESTNA(EQUIV(F$11;ListeData;0)))

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV(F$11;ListeData;0)

Pour infos,
J'ai dans ma macro d'ouverture une commande qui renomme la plage dans la feuille2 (Database) de la première à la dernière cellule non vide en "ListeData"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sheets("Database").Names.Add Name:="ListeData", RefersTo:=Range("A1:A" & [A65536].End(xlUp).Row

Encore Merci pour votre aide!

J'utilise la version 2010 d'excel et mon fichier est au format ".xlsm".

[Philippe Tulliez]

Bonjour,

J'ai dans ma macro d'ouverture une commande qui renomme la plage dans la feuille2 (Database) de la première à la dernière cellule non vide en "ListeData"

N'est-ce pas plus simple de nommer la plage depuis Excel et pour la rendre dynamique, transformer cette plage en tableau ?
Je ne suis pas devant mon PC et ne peux donc pas faire de tests mais manifestement, la plage nommée est de portabilité feuille. Il faut alors modifier la formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(NBCAR(F$11);ESTNA(EQUIV(F$11;Feuil2!ListeData;0)))

(Non testé)

[moh2ss]

Bonjour,

Merci du temps consacré!

N'est-ce pas plus simple de nommer la plage depuis Excel et pour la rendre dynamique, transformer cette plage en tableau ?

Pour des raisons de mises à jour récurentes de la base de données, à chaque ouverture du fichier, ma macro va effacer le contenu de la feuille2(Database) puis importer des données reparties sur plusieurs feuilles d'un autre fichier.
Je les importe et les colle bout à bout dans la feuille2(Database).

La plage est ensuite renommée (ListeData), et je m'en sers comme référence.
Tout est donc automatisé à l'ouverture.

Éventuellement, je pourrais transformer la plage en tableau? Je ne connais pas la commande mais en paufinant..

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(NBCAR(F$11);ESTNA(EQUIV(F$11;Feuil2!ListeData;0)))

ça ne fonctionne pas, Excel me dit:

"Ce type de référence ne peut être utilisé dans une formule Mise en forme Conditionnelle"

Merci

[Philippe Tulliez]

Bonjour,
Avant d'aller plus loin dans les investigations, je vois une différence dans cette ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=ET(NBCAR(F$11);ESTNA(EQUIV(F$11;ListeData;0)))

par rapport à ma proposition qui faisait référence à la cellule $A2
Si les références à contrôler sont en colonne F et commence à la ligne 11 et suivante, après avoir sélectionné les cellules de F11:F99 par exemple, la référence doit être $F11 et pas F$11
On bloque la colonne F et pas la ligne 11 mais j'ajouterais que si la colonne F est la seule à être sélectionnée dans la MFC, la formule peut faire référence à F11 (référence relative) et à condition bien entendu de n'avoir sélectionné que des cellules de la colonne F et en commençant à la ligne 11.

[moh2ss]

Bonjour,

Les cellules à contrôler sont sur une ligne d'où le F$11 et la liste de données en feuille2 est sur une colonne

[Philippe Tulliez]

Bonjour,
Excepté si tu n'as pas affiché tout ton code, la syntaxe que tu as affichée pour créer un nom n'est pas exacte.
Exemples

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub TestDefinedName()
 ' Exemple 1 - Nommer une plage de cellules portabilité classeur
 With ThisWorkbook.Worksheets("db")
  ThisWorkbook.Names.Add Name:="ProductList", RefersTo:=.Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
 End With
 ' Exemple 2 - Nommer une plage de cellule portbilité feuille [db]
 With ThisWorkbook.Sheets("db")
 .Names.Add Name:="ProductList_sh", RefersTo:=.Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
 End With
End Sub

La syntaxe pour une des formules proposée dans ma réponse plus haut.
Avec la référence à une plage nommée de portabilité classeur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NON(ESTNA(EQUIV($A2;ProductList;0)))

Avec la référence à une plage nommée de portabilité feuille ([db])

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NON(ESTNA(EQUIV($A2;db!ProductList_sh;0)))

[moh2ss]

Bonsoir,

Merci beaucoup ça fonctionne!

Un grand merci à vous