Code VBA pour mis en Forme Conditionnelle par mots clefs

Le 14/10/2020 à 16:12

Bonjour,

Sur le fichier ci-joint, j'ai crée une mise en forme conditionnelle par mots clefs, et le soucis que j'ai c'est que ce fichier est annuel et que les personnes qui s'en servent font des copier-coller pur et dur et par conséquent recopient à chaque fois la mise en forme conditionnelle. Et le fichier devient de plus en plus lourd au fil des années.

Par conséquent, j'aimerais savoir si il est possible d'avoir un code VBA pour cette feuille test, permettant de reproduire la mise en forme conditionnelle à chaque mis à jour du fichier

Pour info, le fichier test est à son strict minimum (c'est juste pour l'exemple)

Merci à vous de votre aide

19test.xlsm (153.52 Ko)
Le 14/10/2020 à 17:07

Bonjour,

En effet, la gestion des MFC par excel peut parfois "dériver" et amener des lenteurs. J'ai donc parfois adopté pour un effacement total et une remise en forme propre.

Par exemple

Sub MFC2()

    Cells.FormatConditions.Delete

    With Columns("F:G")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=F1=0,5"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Font.ColorIndex = 28
        .FormatConditions(1).Interior.ColorIndex = 28
        .FormatConditions(1).StopIfTrue = False
    End With

    With Columns("C:F")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ET(LIGNE($A1)>6;C1=0,5)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.ColorIndex = 27
        .FormatConditions(1).StopIfTrue = False
    End With

nom = "elle"

    With Columns("C:F")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ET(LIGNE($A1)>6;$A1=""" & nom & """)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).Interior.ColorIndex = 26
        .FormatConditions(1).StopIfTrue = False
    End With

End Sub
16mfc-par-macro-selon-le-nom.xlsm (15.58 Ko)

je vais regarder ton fichier ... mais cela se fait très bien par apprentissage et ensuite en retouchant le code généré comme ci-dessus (attention aux guillemets il faut parfois être généreux !)

Le 14/10/2020 à 17:21

On pourrait même faire une sous-fonction pour condenser le code

Sub MFC()

    Cells.FormatConditions.Delete
    derL = Range("A" & Rows.Count).End(xlUp).Row

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""AT"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -1003520
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="Malad", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -1003520
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="FORMATION", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599963377788629
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="RECUP CREDIT", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="RECUP DEBIT", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="BONIFICATION", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="RTT", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="Congés", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="NICE OUEST", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10040319
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="NICE NORD", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="NICE EST", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65280
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="REPOS", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=JOURSEM(H$1) =1"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=JOURSEM(H$1) =7"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

End Sub
14test.xlsm (137.31 Ko)
Le 14/10/2020 à 17:23

Un exemple avec des sous-fonctions

Ici on a 4 paramètres

  1. la plage
  2. le mot clé
  3. l'index de couleur de fond
  4. l'index de couleur de police
Sub test()

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

End Sub
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
Le 14/10/2020 à 18:33
Steelson a écrit :

On pourrait même faire une sous-fonction pour condenser le code

Sub MFC()

    Cells.FormatConditions.Delete
    derL = Range("A" & Rows.Count).End(xlUp).Row

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""AT"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -1003520
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="Malad", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -1003520
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="FORMATION", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599963377788629
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="RECUP CREDIT", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="RECUP DEBIT", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="BONIFICATION", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="RTT", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="Congés", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="NICE OUEST", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10040319
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="NICE NORD", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="NICE EST", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65280
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="REPOS", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=JOURSEM(H$1) =1"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    With Range("J1:ABL" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=JOURSEM(H$1) =7"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

End Sub
14test.xlsm (137.31 Ko)

Merci beaucoup de votre réactivité, j'ai fait un test et cela fonctionne parfaitement, vous est-il possible de rajouter juste un code dans MFC que j'ai rajouté dans le fichier test-2.

Et vous est-il possible de m'expliquer un peu comment vous avez crée cette macro, car j'aurais besoin de l'adapter pour beaucoup d'autres fichiers qui fonctionnent sur le même principe mais qui n'utilisent pas les mêmes noms

Encore merci à vous.

18test-2.xlsm (63.41 Ko)
Le 14/10/2020 à 18:46

Je regarderai demain l fichier ... mais voici la "recette" que tu peux appliquer.

  1. Je lance l'enregistreur de macro
  2. Je sélectionne les MFC pour la feuille complète
  3. Je change artificiellement quelque chose, sans vraiment changer car je retape la même valeur, ceci permet d'enregistrer effectivement
  4. J'arrête l'enregistrement d la macro
  5. Je retouche le code
    1. j'enlève les range("XX").select
    2. je calcule la dernière ligne utile
    3. ... à suivre ...
Le 14/10/2020 à 19:12

dans chaque bloc comme suit

    Range("J1:ABL5").Select
    Range("A1").Activate
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""AT"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -1003520
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .Pattern = xlGray8
        .PatternColorIndex = 0
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True

je détermine la dernière ligne

derL = Range("A" & Rows.Count).End(xlUp).Row

je corrige la première comme suit

with Range("J1:ABL" & derL)

j'efface la deuxième ligne qui ne sert à rien

j'ajoute à la fin du bloc

end with

j'enlève tous les selectionmais pas le point qui suit

Rechercher des sujets similaires à "code vba mis forme conditionnelle mots clefs"