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

36test.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
30mfc-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
27test.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
27test.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.

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

Le 04/10/2024 à 08:46

J'ai réouvert le sujet, car j'ai voulu modifier le code en rajoutant une mise en forme conditionnelle.

De telle manière qu'à chaque fois qu'une cellule contenant le "XX" et bien que cette cellule soit en noire et police noire. J'y arrive mais lorsque je tombe sur un samedi-dimanche ou jours fériés la mise en forme conditionnelle n'est pas prioritaire alors que je voudrais que ça le soit.

Merci beaucoup de votre aide

Sub MFC()

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

    Dim cellRange As Range
    Set cellRange = Range("J1:CTR" & derL)

    ' Ajouter la mise en forme pour les cellules contenant "XX" en premier pour lui donner la priorité
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""XX"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Color = 0 ' Couleur noire
            .TintAndShade = 0
        End With
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 0 ' Couleur noire
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = True
    End With

      'Affiche les AT
    With Range("J1:CTR" & 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

      'Affiche les mots contenant malad
    With Range("J1:CTR" & 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

     'Affiche les mots contenant absent
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="Absent", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 16746373
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

      'Affiche les formations
    With Range("J1:CTR" & 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

    'Affiche A CORRIGER
     With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="A CORRIGER", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 0
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

''      'Affiche les recuperatons credit
'    With Range("J1:CTR" & 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

      'Affiche les recuperations debit
    With Range("J1:CTR" & 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

     'Affiche les bonifications
    With Range("J1:CTR" & 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

      'Affiche les RTT
    With Range("J1:CTR" & 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

      'Affiche les conges
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="Cong", _
        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

    'Affiche les HS Prestations Exceptionnelles
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="HS presta. Except", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .Color = 16724484
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

     'Affiche les Prestations Exceptionnelles
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="Presta Except", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .Color = 16724484
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

     'Affiche les Zones Pietonnes
     With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="ZONE PIETONNE", _
        TextOperator:=xlContains
    .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 = 10040319
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    'Affiche les ZP
     With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="ZP", _
        TextOperator:=xlContains
    .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 = 10040319
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

      'Affiche les Vieux-Nice
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="VN", _
        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

    'Affiche les Cartons
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="CARTONS", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
   With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 8421376
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

      'Affiche les detachements
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="DETACHEMENT", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 4324309
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

      'Affiche les repos
    With Range("J1:CTR" & 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

     'Affiche les astreintes
     With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlTextString, String:="ASTREINTE", _
        TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16711681
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 4356523
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    'Affiche les dimanches
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=JOURSEM(J$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

    'Affiche les samedis
    With Range("J1:CTR" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=JOURSEM(J$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

    'Afficher Jours Feries de 2018
    With Range("J1:NJ" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RECHERCHEV(J$1;JFeries!$D$1:$D$11;1;FAUX)"
    .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 = 15189683
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    'Afficher Jours Feries de 2019
    With Range("NK1:ABK" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RECHERCHEV(NK$1;JFeries!$E$1:$E$11;1;FAUX)"
    .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 = 15189683
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    'Afficher Jours Feries de 2020
    With Range("ABL1:APM" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RECHERCHEV(ABL$1;JFeries!$F$1:$F$11;1;FAUX)"
    .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 = 15189683
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    'Afficher Jours Feries de 2021
     With Range("APN1:BDN" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RECHERCHEV(APN$1;JFeries!$G$1:$G$11;1;FAUX)"
    .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 = 15189683
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

     'Afficher Jours Feries de 2022
     With Range("BDO1:BRO" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RECHERCHEV(BDO$1;JFeries!$H$1:$H$11;1;FAUX)"
    .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 = 15189683
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

    'Afficher Jours Feries de 2023
     With Range("BRP1:CFP" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RECHERCHEV(BRP$1;JFeries!$I$1:$I$11;1;FAUX)"
    .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 = 15189683
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

'Afficher Jours Feries de 2024
     With Range("CFQ1:CTR" & derL)
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=RECHERCHEV(CFQ$1;JFeries!$J$1:$J$11;1;FAUX)"
    .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 = 15189683
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = True
    End With

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