Compter les couleurs d'une mise en forme conditionnelle

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Compter les couleurs - Mise en forme conditionnelle

Nous avions déjà avancé des solutions pour compter les cellules en fonction des couleurs qu'elles portaient, avec et sans VBA Excel. Mais dans tous les cas, ces cellules arboraient des mises en forme statiques. Nous n'avions pas encore avancé de solution pour compter les cellules parées de couleurs dynamiques, capables de varier en fonction du contexte. En d'autres termes, nous n'avions pas encore démontré comment compter les cellules surlignées par une règle de mise en forme conditionnelle.



Sur l'exemple illustré par la capture, nous travaillons sur un tableau des chiffres d'affaires réalisés par les commerciaux d'une entreprise. Selon les résultats, les lignes surgissent dans des couleurs différentes et significatives pour en faciliter l'interprétation. Plus la couleur tire vers le vert, plus les données sont considérées comme satisfaisantes. Inversement, plus elle tire vers le rouge, plus les résultats sont considérés comme alarmants.

Ces couleurs sont dirigées par une grille de critères placée sur la droite du tableau. Ce sont des règles de mises en forme conditionnelle qui les appliquent. En regard de ces critères colorés, c'est l'intervention d'une fonction VBA Excel à construire, qui doit dénombrer les ventes appartenant à ces différentes tranches, en fonction des couleurs que portent leurs lignes respectives.

Classeur Excel à télécharger
Pour développer cette nouvelle solution VBA Excel, nous suggérons de baser l'étude sur ce tableau des chiffres d'affaires, avec ces règles de mises en forme prédéfinies.

• Télécharger le classeur nombre-couleurs-mfc.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la commande Propriétés,
• Dans la boîte de dialogue, cocher la case Débloquer et valider par Ok,
• Puis, double cliquer sur le fichier déverrouillé pour l'ouvrir dans Excel,

Nous retrouvons bien le tableau des chiffres colorés par des règles de mise en forme conditionnelle appliquant les critères émis en colonne G. Pour l'instant, aucun décompte de ces couleurs n'est à observer en colonne F voisine. C'est le rôle de la fonction VBA que nous devons construire.



Ces règles, vous pouvez les consulter en cliquant tout d'abord dans l'une des cellules du tableau, par exemple B4. Ensuite, dans le ruban Accueil, il convient de cliquer sur le bouton Mise enforme conditionnelle, puis en bas des propositions de choisir l'option Gérer les règles.



Créer les fonctions
Nous allons le découvrir, l'astuce est très particulière. Elle consiste à contourner un problème de taille. C'est la propriété DisplayFormat d'un objet de type Range qui permet de récupérer le code couleur d'une mise en forme conditionnelle (DisplayFormat.Interior.Color). Mais intégrée dans une fonction, on ne peut pas l'exploiter, Elle retourne une erreur. Nous proposons néanmoins de la créer.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Module1,
• Dans la feuille de code au centre de l'écran, créer la fonction couleur, comme suit :

Function couleur(cellule As String)
couleur = Range(cellule).DisplayFormat.Interior.Color
End Function

Nous récupérons donc le code couleur de MEFC de la cellule passée en paramètre. Mais, l'aide en ligne est très claire à ce sujet, utilisée seule, elle retournera le message d'erreur #VALEUR.

• Revenir sur la feuille Excel (ALT + Tab),
• Puis, dans une cellule vide, par exemple G11, taper et valider la formule suivante :

=couleur("D4")



Comme nous l'annoncions, la fonction ne répond pas favorablement. Elle fonctionnera par évaluation. Nous le comprendrons. Nous allons commencer par l'adapter, pour qu'elle soit plus portable, comme suit :

Private Function couleur(cellule As String, feuille As String)
couleur = Sheets(feuille).Range(cellule).DisplayFormat.Interior.Color
End Function

Cette fonction n'est pas amenée à être utilisée directement. Elle est destinée à être appelée par une autre fonction. Donc, nous la rendons tout d'abord privée (Private). Puis, nous demandons une nouvelle précision en second paramètre, sur le nom de la feuille de destination. Ainsi, nous pourrons l'exploiter partout. Enfin, dans le code, nous descendons dans la hiérarchie (de la feuille à la cellule) pour atteindre ses attributs conditionnels de format.

Compter les couleurs conditionnelles
Ensuite et comme nous l'avons annoncé, l'idée consiste à l'appeler depuis une autre fonction pour évaluer son résultat grâce à la fonction Evaluate. Nous devons lui passer la référence de la feuille et de la cellule en texte (String).

• Sous la première fonction, créer la fonction cpteCouleur, comme suit :

Function cpteCouleur(colonne As Range, cellule As Range) As Integer
Dim laCouleur: Dim chaqueCellule: Dim nb As Byte

Application.Volatile
nb = 0

End Function

Nous la typons comme un entier (As Integer). Son rôle est effectivement de compter les cellules d'une même couleur. Nous la signons avec deux paramètres en attente. Le premier (colonne) représente la plage sur laquelle ce décompte doit être réalisé. Le second (cellule) représente la cellule de référence, portant la couleur à dénombrer dans la plage.

Puis, à l'intérieur de la fonction, nous déclarons trois nouvelles variables. La première n'est pas typée (laCouleur). C'est elle qui doit réceptionner l'information de couleur sur les cellules à évaluer. Nous exploiterons la variable chaqueCellule pour parcourir chaque case à analyser dans la plage passée en premier paramètre. La variable nb, déclarée comme un entier court (Byte), devra être consolidée à chaque couleur concordante trouvée.

Evaluer chaque cellule
Désormais, nous devons engager la boucle dont nous parlions pour évaluer chaque cellule analysée dans la plage souhaitée.

• A la suite du code, ajouter les instructions VBA suivantes :

...
For Each chaqueCellule In colonne
laCouleur = Evaluate("couleur(""" & chaqueCellule.Address & """,""" & chaqueCellule.Worksheet.Name & """)")

Next chaqueCellule
...

Nous engageons notre objet chaqueCellule dans la plage (colonne) passée en premier paramètre. C'est ainsi qu'il prend son type et devient un objet Range, représentant les cellules une à une, dans cette boucle For Each. C'est alors que nous engageons la fonction Evaluate pour appeler la fonction privée couleur. Grâce à la propriété Address de l'objet chaqueCellule, nous lui passons les coordonnées de la cellule en cours d'analyse, en premier paramètre. Mais attention, les côtes sont triplées ("""). En effet, l'expression d'évaluation est déjà écrite entre doubles côtes et les paramètres sont textuels. Ils doivent eux aussi être encadrés. C'est cette astuce de syntaxe qui permet d'y parvenir. En second paramètre, nous lui passons le nom de la feuille cible (Worksheet.Name), lui aussi encadré de doubles côtes, selon la même technique.

Comparer les couleurs
A chaque passage dans cette boucle, nous devons comparer la couleur conditionnelle portée par la cellule en cours d'analyse avec celle de la cellule de référence passée en second paramètre de notre fonction appelante. Donc, une instruction conditionnelle s'impose pour réaliser le test.

• A la suite du code de la boucle, créer l'instruction conditionnelle suivante :

...
If (laCouleur = cellule.Interior.Color) Then
nb = nb + 1
End If
...

Si la couleur dynamique pour la cellule en cours d'analyse équivaut à celle de la cellule de référence, nous incrémentons la variable nb (nb = nb + 1), pour réaliser le décompte final dans ce processus récursif.

Retourner le résultat
A l'issue du traitement, cette fonction doit retourner la valeur de son comptage, par son propre nom, comme vous le savez. Donc, nous devons l'affecter.

• Après la boucle, créer l'affectation suivante :

...
Next chaqueCellule

cpteCouleur = nb

End Function
...

Nous pouvons maintenant tester notre création de fonctions imbriquées.

• Revenir sur la feuille Excel (ALT + Tab),
• Sélectionner la première case du tableau de synthèse en cliquant sur la cellule F4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Appeler la nouvelle fonction par son nom, suivie d'une parenthèse ouvrante : cpteCouleur(,
• Désigner par exemple la dernière colonne du tableau, soit la plage de cellules D4:D51,
• Puis, la figer en enfonçant la touche F4 du clavier, ce qui donne : $D$4:$D$51,

En effet, nous allons répliquer la formule sur les lignes du dessous. Or, les bornes de la plage d'analyse ne doivent pas bouger.

• Taper un point-virgule (;) pour passer dans l'argument de la cellule de référence,
• Cliquer sur la cellule de couleur voisine à la cellule de la formule, soit : G4,

Cette fois, nous ne devons pas la figer. Au fil de la réplication, ce sont les autres cellules de couleur qui devront servir de référence, tour à tour.

• Fermer la parenthèse de la fonction cpteCouleur,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons active la cellule du résultat. Et à ce propos, il indique qu'une seule personne est sanctionnée par cette couleur rouge. Vous pouvez très facilement le vérifier en faisant défiler le tableau des chiffres d'affaires.

• Double cliquer sur la poignée du résultat pour répandre la logique sur les lignes du dessous,

Comme vous pouvez l'apprécier, notre nouvelle fonction est effectivement en capacité de compter les cellules de couleurs, impliquées dans des mises en forme conditionnelles. Bien entendu et pour finir, si vous modifiez des chiffres dans le tableau d'origine pour traverser des catégories, vous constatez que les résultats de notre formule, s'actualisent aussitôt.