Changer la couleur en fonction de la donnée saisie

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 à la saisie

Avec cette nouvelle astuce VBA Excel, nous allons apprendre à parer automatiquement les cellules de couleurs spécifiques en fonction des saisies émises.



Sur l'exemple illustré par la capture, nous travaillons sur un planning des absences des salariés. Ces absences sont marquées par des couleurs spécifiques en fonction des causes. Ces causes sont listées dans une colonne sur la droite du tableau avec leurs couleurs associées. Lorsque l'utilisateur valide une raison pour une date et pour un salarié dans le planning, la cellule se pare automatiquement de la signalétique proposée par la rangée des causes.

L'avantage et l'intérêt par rapport à des règles de mise en forme prédéfinies sont manifestes. Dans la colonne placée sur la droite du tableau, l'opérateur peut créer de nouvelles raisons d'absences avec leurs signalétiques. Dès lors, elles sont automatiquement prises en compte lors de la saisie dans le planning, sans avoir dû créer une nouvelle règle en amont, pour la considérer.

Classeur Excel à télécharger
Pour développer cette solution, nous suggérons d'appuyer l'étude sur cette feuille offrant ce planning des absences et cette colonne des causes.

• Télécharger le classeur couleurs-automatiques.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 planning des absences au centre de la feuille. Il est déjà enrichi de certaines informations. La rangée des raisons possibles est énoncée sur la droite en colonne L, avec pour chaque cause, une couleur spécifique. A ce stade bien sûr, si vous saisissez une cause d'absence dans le planning, les couleurs ne sont pas appliquées. Le code VBA ne réagit pas puisqu'il n'existe pas encore. Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous remarquez que deux plages nommées existent. La première (planning) représente les cellules du planning des absences. La seconde (raisons) représente la colonne des causes des absences. Nous exploiterons ces noms en VBA.

Contrôler la saisie utilisateur
Pour appliquer automatiquement des couleurs, en fonction de la saisie de l'utilisateur, nous devons créer une procédure événementielle capable de se déclencher à chaque modification de données. Nous en avons l'habitude, il s'agit d'associer l'événement Change à la feuille active.

• 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),

C'est ainsi que nous affichons la feuille de code VBA associée à la feuille du planning. Elle apparaît au centre de l'écran. Elle est naturellement vierge à ce stade.

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

Celui-ci désigne la feuille active et cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange. Ce n'est pas celle qui nous intéresse car il n'est pas question de déclencher un code VBA au changement de sélection mais au changement de valeur.

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

Cette fois, nous créons la procédure Worksheet_Change. C'est bien celle qui nous intéresse. De fait, la précédente peut être supprimée.

La déclaration des variables
La variable Target passée en paramètre de la procédure, représente déjà la cellule modifiée par l'utilisateur sur la feuille. Nous avons néanmoins besoin de deux variables supplémentaires. La première doit représenter la cellule de la cause correspondant à la saisie, dans la plage nommée raisons. La seconde doit représenter l'intersection entre les cellules du planning et la cellule modifiée par l'utilisateur. C'est ainsi que nous saurons s'il s'agit bien d'une case à considérer par le code VBA.

• Dans les bornes de la procédure, ajouter les deux déclarations et l'affectation suivantes :

...
Dim cellule As Range: Dim intersection As Range

Set intersection = Application.Intersect([planning], Target)
...

Les deux objets doivent représenter des cellules, c'est pourquoi nous les typons comme des Range. Ensuite, nous initialisons l'objet intersection sur une plage de cellules résultant d'un croisement. En effet, nous exécutons la méthode Intersect sur deux plages : le planning et la ou les cellules concernées par la modification. Elle renvoie un objet de type Range qui représente la ou les cellules au croisement. C'est désormais notre objet intersection qui pilote cette plage.

Optimiser le code
Ensuite, il n'est pas question de déclencher le code à chaque modification de valeur sur la feuille. Ces modifications doivent concerner uniquement les cellules du planning. Pour cela, il suffit de réaliser un test sur la plage nommée intersection et que nous venons d'initialiser.

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

...
If Not intersection Is Nothing Then

End If
...

S'il existe bien au moins une cellule au croisement de la modification et du planning, alors nous décidons de poursuivre l'exécution du code VBA.

La cause de l'absence
La suite du développement est cousue de fil blanc. Il s'agit d'intercepter la saisie de l'utilisateur sur le planning et de rechercher la cause de l'absence dans la plage nommée raisons.

• Dans l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
Set cellule = [raisons].Find(Target)
If Not cellule Is Nothing Then

End If
...

Nous initialisons notre objet cellule grâce à la méthode Find exercée sur la plage raisons, celle des absences. Cette méthode recherche dans une plage (raisons) l'élément mentionné en premier paramètre (target), soit la ou les cellules modifiées dans le planning. Elle renvoie un objet de type Range. Grâce à lui, nous allons pouvoir récupérer les attributs de la cause de l'absence pour les appliquer sur les cellules impliquées dans le planning, dans la mesure où la cause a été trouvée (If Not cellule Is Nothing Then).

Appliquer les couleurs
Si la cause de l'absence est trouvée, nous devons répliquer ses couleurs dans le planning. C'est une simple question d'affection des propriétés des polices entre la cellule appelante (Target) et la cellule trouvée (cellule).

• Dans les bornes de cette nouvelle instruction conditionnelle, créer le bloc With suivant :

...
With Target
.Interior.Color = cellule.Interior.Color
.Font.Color = cellule.Font.Color
End With
...

Le bloc With permet de ne pas répéter l'objet Target pour lequel nous avons deux propriétés à régler. Nous prélevons tout d'abord la couleur de fond de la cellule de la cause de l'absence (cellule.Interior.Color), puis celle de sa couleur de police (cellule.Font.Color), pour les appliquer sur la ou les cellules concernées dans le planning.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),

Désormais, si vous ajoutez une cause d'absence dans le planning, la ou les cellules concernées se parent des couleurs définies dans la colonne nommée raisons. Pour inscrire des motifs sur plusieurs cellules à la fois, il suffit de les sélectionner à la souris, d'écrire la cause et de la valider par le raccourci clavier CTRL + Entrée pour la répandre sur toutes les cellules de la plage présélectionnée.



Si vous créez un nouveau motif dans la colonne des raisons, que vous l'entrez dans le planning, à validation, il est automatiquement repéré dans sa signalétique personnalisée. Voilà entre autres, tout l'intérêt du code VBA. Il s'agit de souplesse et d'adaptation.