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
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