Couleurs automatiques par tranches de valeurs

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


Sujets que vous pourriez aussi aimer :


Couleurs automatiques par tranches

Avec ce nouveau développement VBA Excel, nous proposons de créer un code capable de faire réagir les couleurs des cellules de la feuille, en fonction des valeurs qu'elles portent.



Sur l'exemple illustré par la capture, nous travaillons à partir d'un tableau relatant les chiffres d'affaires de quelques commerciaux, dans une colonne intitulée Réalisé. Sur la droite, une colonne intitulée Paliers, dicte les règles. Dès qu'un chiffre saisi, dépasse l'un des paliers, il arbore automatiquement sa couleur. C'est le code VBA qui réagit à l'inscription de nouvelles données pour livrer une synthèse visuelle saisissante.

Cette solution remplace avantageusement les règles de mise en forme conditionnelle. Elle est plus souple et peut facilement s'adapter à de nouveaux paliers, pour un code très simple, nous le verrons. De plus, sans le VBA, il faudrait bâtir autant de règles de mise en forme conditionnelle qu'il y a de paliers à observer.

Classeur Excel à télécharger
Pour appuyer ce nouveau développement, nous suggérons de récupérer le tableau de ces ventes.

• Télécharger le classeur couleurs-automatiques-paliers-valeurs.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons bien le tableau des ventes avec sur la droite, la colonne des paliers. Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous constatez que la colonne des chiffres d'affaires est reconnue sous l'intitulé ca tandis que la colonne des paliers est reconnue sous l'intitulé paliers. Nous piloterons ces noms de plages par le code VBA.

Code VBA à la saisie
Pour que les couleurs soient automatiquement appliquées après la saisie d'un montant, nous devons commencer par créer une procédure événementielle capable de déclencher son traitement dès qu'une modification est détectée dans une cellule de la feuille.

• 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 Feuil1 (couleursAuto),

De cette manière, nous affichons la feuille de code VBA associée à la feuille Excel. Elle apparaît au centre de l'écran. Elle est vierge pour l'instant.

• En haut de cette feuille de code, déployer la liste déroulante de gauche,
• Dans les propositions, choisir l'élément Worksheet,

Il désigne la feuille active. Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Cependant, nous ne souhaitons pas engager un traitement au changement de sélection mais au changement de valeur.

• Déployer alors la liste déroulante de droite,
• Dans les propositions, choisir l'événement Change,

C'est ainsi que nous créons la procédure événementielle Worksheet_Change. De fait, la précédente peut être supprimée.

Cibler la zone
L'analyse que nous devons entreprendre ne doit intervenir que lorsque la modification concerne un chiffre d'affaires, donc que la cellule cible appartient bien à la plage nommée ca. Pour cela, le VBA offre une méthode très intéressante. Elle se nomme Intersect. Elle permet de vérifier si l'intersection entre deux plages conduit bien à une autre plage ou a défaut à une cellule. En d'autres termes, elle permet de savoir si la cellule modifiée appartient bien à la zone ciblée.

• Dans les bornes de la procédure, ajouter les lignes VBA suivantes :

...
On Error Resume Next

If Not Application.Intersect([ca], Target) Is Nothing And Target.Count = 1 Then

End If
...

La gestion d'exception (On Error Resume Next) permet au code VBA de ne pas réagir en cas de saisie erronée, ne correspondant à aucun palier. L'objet Target est passé en paramètre de la procédure. Il représente la ou les cellules modifiées par l'utilisateur sur la feuille Excel. Grâce à la méthode Intersect de l'objet Application, nous le recoupons avec la plage des chiffres (ca). Si cette intersection n'est pas vide (Not ... Is Nothing) et que dans le même temps nous savons que la modification ne concerne qu'une cellule (Target.Count = 1), alors nous décidons d'enclencher l'analyse.

Trouver le bon palier
Désormais, pour trouver le palier correspondant à la saisie modifiée ou ajoutée, l'astuce consiste à réaliser une recherche approximative avec la fonction Equiv. De cette manière, le code VBA se calera sur le palier le plus proche, directement inférieur au montant inscrit. Attention néanmoins, en anglais cette fonction se nomme Match, donc en VBA aussi.

• Dans l'instruction conditionnelle, ajouter la ligne VBA suivante :

...
p = Application.Match(Target, [paliers], 1)
...

Nous engageons donc une recherche de la valeur modifiée (Target) dans la plage des paliers ([paliers]) avec une correspondance approximative (1). La ligne de la cellule concordante sera ainsi inscrite dans la variable p. Nous ne l'avons pas déclarée. Nous aurions dû le faire. Mais nous l'avons déjà évoqué, le VBA est permissif. Il la déclare et la type implicitement.

Répliquer la couleur du palier
Maintenant que nous connaissons la ligne du palier, nous pouvons pointer précisément dessus pour ponctionner sa couleur de fond ainsi que sa couleur de police, pour les répliquer sur la cellule modifiée dans la colonne ca.

• A la suite du code de l'instruction conditionnelle, ajouter les deux lignes VBA suivantes :

...
Target.Interior.Color = Range("paliers")(p).Interior.Color
Target.Font.Color = Range("paliers")(p).Font.Color
...

Par voie hiérarchique, nous accédons aux attributs de remplissage (Interior.Color) de la cellule modifiée ainsi qu'à sa couleur de police (Font.Color) pour leur affecter respectivement les attributs du palier. Pour cela et c'est une astuce savoureuse, nous pointons sur la ligne trouvée (p) dans la plage des paliers (Range("paliers")) en récupérant les valeurs des propriétés de mêmes natures.

• Enregistrer les modifications (CTRL + S),
• Basculer sur la feuille Excel (ALT + Tab),
• Dans la colonne des chiffres, saisir quelques montants,

Comme vous pouvez l'apprécier, à chaque validation, la cellule du chiffre inscrit se pare de sa couleur représentative en fonction du palier qu'elle a atteint. C'est instantanément, selon la densité de couleurs positives ou négatives, que l'utilisateur sait si les résultats sont satisfaisants ou insuffisants. Il s'agit donc d'une solution d'autant plus efficace que les informations à analyser sont nombreuses.