Formatage conditionnel VBA

Mise en forme conditionnelle dans Excel VBA

Nous pouvons appliquer une mise en forme conditionnelle à une cellule ou à une plage de cellules dans Excel. Un format conditionnel est un format qui s'applique uniquement aux cellules qui répondent à certains critères, par exemple des valeurs supérieures à une valeur particulière, des valeurs positives ou négatives, ou des valeurs avec une formule particulière, etc. Cette mise en forme conditionnelle peut également être effectuée dans la programmation Excel VBA en utilisant le ' Format Conditions Collection ' dans la macro / procédure

Format Condition est utilisé pour représenter un format conditionnel qui peut être défini en appelant une méthode qui renvoie une variable de ce type. Il contient tous les formats conditionnels pour une seule plage et ne peut contenir que trois conditions de format.

FormatConditions.Add / Modify / Delete est utilisé dans VBA pour ajouter / modifier / supprimer des objets FormatCondition à la collection. Chaque format est représenté par un objet FormatCondition. FormatConditions est une propriété de l'objet Range et Add a les paramètres suivants avec la syntaxe ci-dessous:

FormatConditions.Add (Type, Opérateur, Formule1, Formule2) 

La syntaxe de formule Ajouter a les arguments suivants:

  • Type: obligatoire, indique si le format conditionnel est basé sur la valeur présente dans la cellule ou une expression
  • Opérateur: facultatif, représente l'opérateur à utiliser avec une valeur lorsque 'Type' est basé sur la valeur de la cellule
  • Formule1: facultative, représente la valeur ou l'expression associée au format conditionnel.
  • Formula2: facultatif, représente la valeur ou l'expression associée à la seconde partie du format conditionnel lorsque le paramètre: 'Operator' est soit 'xlBetween' ou 'xlNotBetween'

FormatConditions.Modify a également la même syntaxe que FormatConditions.Add.

Voici la liste de certaines valeurs / énumérations qui peuvent être prises par certains paramètres de 'Ajouter' / 'Modifier':

Exemples de mise en forme conditionnelle VBA

Vous trouverez ci-dessous des exemples de mise en forme conditionnelle dans Excel vba.

Vous pouvez télécharger ce modèle de formatage conditionnel VBA ici - Modèle de formatage conditionnel VBA

Exemple 1

Supposons que nous ayons un fichier Excel contenant le nom et les notes de certains élèves, et que nous souhaitons déterminer / mettre en évidence les marques en gras et de couleur bleue supérieure à 80, et en gras et rouge en couleur inférieure à 50. Voyons les données contenues dans le fichier:

Nous utilisons la fonction FormatConditions.Add comme ci-dessous pour accomplir cela:

  • Accédez à Developer -> Visual Basic Editor:

  • Cliquez avec le bouton droit sur le nom du classeur dans le volet 'Projet-VBAProject' -> 'Insertion' -> 'Module'.

  • Maintenant, écrivez le code / la procédure dans ce module:

Code:

 Sous-formatage () End Sub 

  • Définissez la variable rng, condition1, condition2:

Code:

 Sous-formatage () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition End Sub 

  • Définissez / fixez la plage sur laquelle le formatage conditionnel doit être souhaité à l'aide de la fonction VBA 'Range':

Code:

 Sous-formatage () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") End Sub 

  • Supprimez / effacez toute mise en forme conditionnelle existante (le cas échéant) de la plage, en utilisant 'FormatConditions.Delete':

Code:

 Sous-formatage () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete End Sub

  • Maintenant, définissez et définissez les critères pour chaque format conditionnel, en utilisant 'FormatConditions.Add':

Code:

 Sous-formatage () Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition Set rng = Range ("B2", "B11") rng.FormatConditions.Delete Définit condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80 ") Définir condition2 = rng.FormatConditions.Add (xlCellValue, xlLess," = 50 ") End Sub 

  • Définir et définir le format à appliquer pour chaque condition

Copiez et collez ce code dans votre module de classe VBA.

Code:

Sous-formatage () 'Définition des variables: Dim rng As Range Dim condition1 As FormatCondition, condition2 As FormatCondition' Fixation / Réglage de la plage sur laquelle le formatage conditionnel doit être souhaité Set rng = Range ("B2", "B11") 'To supprimer / effacer toute mise en forme conditionnelle existante de la plage rng.FormatConditions.Delete 'Définition et définition des critères pour chaque format conditionnel Définir condition1 = rng.FormatConditions.Add (xlCellValue, xlGreater, "= 80") Définir condition2 = rng.FormatConditions. Add (xlCellValue, xlLess, "= 50") 'Définition et réglage du format à appliquer pour chaque condition Avec condition1 .Font.Color = vbBlue .Font.Bold = True Fin avec avec condition2 .Font.Color = vbRed .Font. Gras = vrai fin avec fin sous

Maintenant, lorsque nous exécutons ce code à l'aide de la touche F5 ou manuellement, nous voyons que les marques inférieures à 50 sont mises en surbrillance en gras et en rouge, tandis que celles supérieures à 80 sont mises en évidence en gras et en bleu comme suit:

Remarque: Certaines des propriétés de l'apparence des cellules formatées qui peuvent être utilisées avec FormatCondition sont:

Exemple # 2

Disons que dans l'exemple ci-dessus, nous avons également une autre colonne qui indique que l'étudiant est un «Topper» s'il obtient plus de 80 points, sinon «réussite / échec» est écrit contre eux. Nous souhaitons maintenant mettre en évidence les valeurs indiquées comme «Topper» comme Gras et Bleu. Voyons les données contenues dans le fichier:

Dans ce cas, le code / la procédure fonctionnerait comme suit:

Code:

 Sub TextFormatting () End Sub 

Définir et définir le format à appliquer pour chaque condition

Code:

 Sub TextFormatting () With Range ("c2: c11"). FormatConditions.Add (xlTextString, TextOperator: = xlContains, String: = "topper") With .Font .Bold = True .Color = vbBlue End With End With End Sub 

Nous pouvons voir dans le code ci-dessus que nous souhaitons tester si la plage: 'C2: C11 ”contient la chaîne:“ Topper ”, donc le paramètre:“ Operator ”de' Format.Add 'prend l'énumération:” xlContains ”, pour tester cette condition dans la plage fixe (c'est-à-dire C2: C11), puis effectuez le formatage conditionnel requis (changements de police) sur cette plage.

Maintenant, lorsque nous exécutons ce code manuellement ou en appuyant sur la touche F5, nous voyons que les valeurs de cellule avec 'Topper' sont mises en évidence en bleu et en gras:

Remarque: Nous avons donc vu dans les deux exemples ci-dessus comment la méthode 'Add' fonctionne en cas de critère de valeur de cellule (numérique ou chaîne de texte).

Voici quelques autres instances / critères qui peuvent être utilisés pour tester et donc appliquer le formatage conditionnel VBA sur:

  • Format par période
  • État moyen
  • Condition de l'échelle de couleur
  • IconSet condition
  • État de la barre de données
  • Valeurs uniques
  • Dupliquer les valeurs
  • Top 10 des valeurs
  • Condition centile
  • Condition des blancs, etc.

Avec différentes conditions à tester, différentes valeurs / énumérations sont prises par les paramètres de 'Add'.

Points à retenir sur le formatage conditionnel VBA

  • La méthode 'Add' avec 'FormatConditions' est utilisée pour créer un nouveau format conditionnel, la méthode 'Delete' pour supprimer tout format conditionnel et la méthode 'Modify' pour modifier tout format conditionnel existant.
  • La méthode 'Add' avec 'FormatConditions Collection' échoue si plus de trois formats conditionnels sont créés pour une seule plage.
  • To apply more than three conditional formats to a range using the ‘Add’ method, we can use ‘If’ or ‘select case’.
  • If the ‘Add’ method has its ‘Type’ parameter as: ‘xlExpression’, then the parameter ‘Operator’ is ignored.
  • The parameters: ‘Formula1’ and ‘Formula2’ in the ‘Add’ method can be a cell reference, constant value, string value, or even a formula.
  • The parameter: ‘Formula2’ is used only when the parameter: ‘Operator’ is either ‘xlBetween’ or ‘xlNotBetween’, else it is ignored.
  • To remove all the conditional formatting from any worksheet, we can use the ‘Delete’ method as follows:
Cells.FormatConditions.Delete