Boucle sur mise en forme conditionnelle selon liste de mots clés

bonjour à tous,

je me permets de poster ce sujet, car malgré de nombreuses recherches, je ne trouve pas de solution à ma problématique qui est la suivante :

par ce qui est décrit ci dessous, je met en évidence (par mise en forme conditionnelle) des cellules d'une colonne contenant des phrases, dans lesquelles on retrouve un ou des mots clés, ces derniers étant listés dans des cellules d'une autre colonne.

Cependant, ma liste de mots clés peut être modifiée, ou très longue. l'exemple joint présente 3 mots clés et je souhaiterais pouvoir créer une macro/VBA permettant de faire une recherche cellule par cellule jusqu'à la dernière cellule non vide de ma liste de mots clés.

le fichier joint illustre mes propos:

en feuil1 colonne A, ma liste de mots clés

en feuil2 colonne A, mes phrases

je mets ici en évidence les phrases de feuil2 colonne A qui contienne un ou des mots clés de feuil1 colonne A.

pour ceci j'ai fait ce qui suit (avec un bouton, recherche en feuil1):

Sub searchkeyword()

Sheets("Feuil2").Select

Range("A1:A20").Select

Application.CutCopyMode = False

Selection.FormatConditions.Add Type:=xlTextString, String:="=Feuil1!$A$2" _

, TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Application.CutCopyMode = False

Selection.FormatConditions.Add Type:=xlTextString, String:="=Feuil1!$A$3" _

, TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

Application.CutCopyMode = False

Selection.FormatConditions.Add Type:=xlTextString, String:="=Feuil1!$A$4" _

, TextOperator:=xlContains

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

End With

Selection.FormatConditions(1).StopIfTrue = False

End Sub

le souci est que; si j'ai 150 mots clés, je dois copier-coller 150 fois le bloc, ce qui n'est pas très pratique et peut donner une VBA bien trop longue.

Auriez-vous des idées permettant de réduire les lignes de cette VBA ? Je pensais peut-être à une boucle, mais je ne sais pas faire.

Je suis novice en VBA et cherche petit à petit à comprendre cet environnement donc, merci de votre indulgence. Par ailleurs, j'espère que ce sujet n'a pas été traité autre part sur ce forum, j'ai beaucoup cherché mais peut-être pas avec les bons mots clés. Un coup de main sur cette VBA me permettra de mieux chercher la prochaine fois !

par avance merci de vos retours et de vos idées !

Cordialement,

Bonjour

Je sais que les macros sont à la mode car, pourquoi faire compliqué alors qu'on pourrait faire simple.

Sur ce fichier en retour, une formule nommée et une MFC classique.

Cordialement

bonjour,

En effet... pourquoi faire compliqué alors qu'on pourrait faire simple.

merci vivement pour ce retour très rapide qui répond parfaitement à mes attentes.

cordialement,

@Amadéus ... mes respects au grand expert ! je ne savais pas qu'on pouvait mettre dans une MFC une formule a priori matricielle dans une feuille normale ! je suis scotché par la solution.

Bonjour Steelson

Juste pour confirmation de l'utilisation de formule matricielle dans une MFC et ailleurs aussi.

Dans ce second fichier, la matricielle, en tant que formule, n'est plus utilisée pour la MFC

C'est le Nom de cette formule matricielle nommée qui est utilisé.

Pour résumer: Dans une MFC ou pour Définir un Nom, une formule matricielle ne nécessite pas de validation particulière.

Cordialement

Bonjour à tous

Une variante de la solution d'Amadeus en utilisant un tableau structuré plutôt qu’une formule DECALER

Pour info, dans 365, la formule matricielle dans les cellules ne nécessiterait plus non plus de validation par CTRL Shift Entrée et n'afficherait plus les {

Mais cela semble une légende ou bien un nouveauté pour les seuls Insider...

Edit : j'ai 2 versions 365, une perso et une pro. Perso plus à jour et cela marche effectivement sans CTRL Shift Entrée

Bonjour à tous,

Merci encore Amadeus et merci 78chris pour ces solutions !

Une petite question supplémentaire; dans ma Feuil1 je cherche à importer des données, par exemple d'un site web, et de faire, donc, ma recherche de mots clés sur ces données importées.

Cependant, lorsque j'importe mes données Web, la MFC est effacée (suite à cells.clear avant le nouvel import, pour repartir propre) ou appliqué uniquement à la plage définie avant l'importation (car décalage/création de colonne lors de l'import, donc pas aux nouvelles données importées), je dois donc l'appliquer manuellement à nouveau. Ce n'est pas très gênant, mais je souhaiterais qu'une fois les données importées, ma MFC soit appliquée automatiquement (c'est aussi pour cela que j'étais passé par une macro initialement, pour faire l'importation web et la mise en forme automatiquement).

Auriez-vous des idées pour que cette MFC reste présente et active, malgré une suppression/remplacement (via l'importation de données externe) en Feuil1 ?

par avance merci.

cordialement,

Remplace cells.clear par Cells.ClearContents pour conserver la mise en forme

Remplace cells.clear par Cells.ClearContents pour conserver la mise en forme

bonjour,

merci, mais malheureusement le résultat reste non conforme, après Cells.clearContents, la mise en forme s'applique à =$A$1;$A$112:$A$1048576 (au lieu de =$A:$A)

RE

Je pense que tu devrais mettre les données de la feuille 2 également sous forme de tableau structuré (ListObject).

Partir de la feuille vide, mettre un titre, mettre sous forme de tableau et prévoir la MFC sur l'unique ligne sous le titre

Quand tu vas utiliser un copier/collage spécial valeurs des nouvelles données sur cette ligne, le tableau va s'agrandir et les nouvelles lignes vont hériter de la MFC.

Ne pas supprimer de lignes. Si tu dois en enlever une partie vide avec Clear.Contents, trie le tableau puis supprime les lignes vides en bas. Ensuite tu colleras sous la dernière ligne et le tableau structuré s'adaptera

Ceci devrais maintenir la MFC en état de fonctionnement.

Je finis par penser qu'il vaut mieux programmer les MFC par macro en effaçant toutes les anciennes MFC, en appliquant bien sûr les solutions présentées ci-dessus.

En effet, on le voit encore, la gestion des MFC n'est pas très claire lors des modifications. Excel divise les plages, multiplie les MFC qui deviennent vite ingérables manuellement, et surtout finissent par ralentir le fichier.

Je m'étais du reste fait un modèle

Sub MFC(plage, nom, cFond, cPolice)

    adresse = plage.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
    With plage
        .FormatConditions.Add Type:=xlExpression, Formula1:="=" & adresse & "=""" & nom & """"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Font.ColorIndex = cPolice
        .FormatConditions(1).Interior.ColorIndex = cFond
        .FormatConditions(1).StopIfTrue = False
    End With

End Sub

Sub test()

    Cells.FormatConditions.Delete
    MFC Selection, "coucou", 46, 2

End Sub

Reste à l'adapter à la solution mots-clés.

NOTA : ce message qui fait changer de page ne doit pas occulter la remarque de Chris sur les tableaux structurés.

lorsque j'importe mes données Web, la MFC est effacée

Auriez-vous des idées pour que cette MFC reste présente et active, malgré une suppression/remplacement (via l'importation de données externe) en Feuil1 ?

Une solution par macro simple qui s'appuie sur la proposition d'Amadéus https://forum.excel-pratique.com/viewtopic.php?p=832434#p832434

Sub searchkeyword()

    Sheets("Feuil2").Activate
    Cells.FormatConditions.Delete
    MFC Columns("A"), 46, 2

End Sub

Sub MFC(plage, cFond, cPolice)

    With plage
        .FormatConditions.Add Type:=xlExpression, Formula1:="=Formule"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Font.ColorIndex = cPolice
        .FormatConditions(1).Interior.ColorIndex = cFond
        .FormatConditions(1).StopIfTrue = False
    End With

End Sub

avec Formule :

=SOMME(ESTNUM(CHERCHE(SI(Key_Words<>"";Key_Words);Feuil2!D10))*LIGNE(Key_Words))>0

Mais on doit pouvoir aussi le faire via un tableau structuré comme préconisé par Chris

Rechercher des sujets similaires à "boucle mise forme conditionnelle liste mots cles"