Comment identifier les cellules identiques en 1 clic sur Excel (couleur selon valeur) ?

Dans ce tutoriel, je vais vous montrer comment créer une macro VBA capable de détecter toutes les valeurs en double dans une sélection et de les colorer automatiquement, en appliquant une couleur différente pour chaque valeur !

Ce procédé vous permettra de visualiser rapidement chaque doublon distinct grâce à des codes couleurs uniques. Suivez bien chaque étape, et d’ici quelques minutes, vos données prendront vie en couleur !

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer notre tutoriel, nous allons partir d'un tableau de données simple qui reprend les différentes tâches de réalisation d’un projet d’entreprise :

Ce tableau contient plusieurs valeurs en double dans la colonne "Tâche" et également dans la colonne Opérateur.

Notre objectif est de détecter ces valeurs et de leur appliquer une couleur unique pour chaque valeur doublon, de façon à les identifier visuellement.

Et pour réaliser cette opération, nous allons devoir mettre en place une petite macro VBA.

Le Visual Basic for Applications (VBA) est un langage de programmation intégré à Excel, qui permet de créer des automatismes appelés des "macros".
Ces macros peuvent effectuer des tâches répétitives de manière automatique, ce qui est très utile pour accélérer des processus, comme dans notre cas, où nous voulons détecter les doublons et appliquer des couleurs automatiquement.
Imaginez devoir colorer manuellement chaque doublon : cela prendrait un temps fou, surtout sur de grandes bases de données !
Avec VBA, quelques lignes de code suffisent pour automatiser ce processus.
Dans le cadre de ce tutoriel, nous n’allons qu’effleurer l’étendue de la puissance du VBA dans Excel, mais si vous souhaitez aller plus loin, vous pouvez consulter mon livre dédié au VBA en cliquant ici.

2. Création d’un bouton pour exécuter la macro

Pour faciliter l'exécution de notre macro, nous allons créer un bouton qui nous permettra de la lancer d’un simple clic, sans devoir passer par le menu VBA.
Dans Excel, nous nous rendons dans l’onglet Insertion de la barre de menu. Ensuite, dans le groupe Texte, nous cliquons sur Zone de texte.
Le curseur de la souris prend alors la forme d’une croix, que nous faisons glisser sur la feuille de calcul pour dessiner la zone de texte à l’endroit désiré.
Une fois la zone de texte créée, nous saisissons un texte descriptif comme « Distinguer les valeurs » pour indiquer sa fonction.
Nous pouvons maintenant la mettre en forme pour qu’elle soit bien visible.
Pour cela, nous nous rendons dans le menu « Format de la forme » pour accéder aux options de personnalisation.
Par exemple, nous appliquons une couleur de fond, ajustons la taille du texte et centrons le texte dans la zone.
Pour modifier plusieurs caractéristiques d’un coup, nous pouvons également choisir l’un des styles proposés dans les options de mise en forme, ce qui nous permettra de créer un bouton élégant et facilement reconnaissable.
Ensuite, nous allons attribuer une nouvelle macro automatiquement à ce bouton.
Pour ce faire, nous effectuons un clic droit sur la zone de texte, puis sélectionnons Attribuer une macro….
Dans la boîte de dialogue qui s’ouvre, nous saisissons le nom que nous souhaitons donner à cette macro, par exemple « distinguerDoublons », puis nous cliquons sur Nouvelle pour créer une nouvelle macro.
Elle sera automatiquement liée à ce bouton.
Excel ouvrira alors l’éditeur VBA directement dans un module, où nous pourrons commencer à écrire notre code.
Ce module est une page blanche où il est déjà indiqué le nom de la macro associée à la zone de texte.
C'est ici que nous allons entrer notre code pour automatiser la coloration des doublons.
En effet, lorsque nous cliquerons sur le bouton, toutes les lignes que nous allons saisir entre le « Sub » et le « End Sub » vont s’exécuter automatiquement.

3. Écrire le code VBA pour colorier les doublons

Nous allons maintenant entrer dans le vif du sujet : l’écriture du code de notre macro.
Cette macro sera constituée de trois étapes principales :

• La création d'un « dictionnaire » pour compter chaque occurrence de valeur.
• L’identification des doublons.
• L’application d’une couleur unique pour chaque valeur doublon.

Pour commencer, nous aller déclarer les quatre variables que nous aurons besoin d’utiliser tout au long de notre macro.
Une variable est un espace de stockage temporaire en mémoire qui permet de conserver une donnée pendant l’exécution de la macro.
Chaque variable a un type, qui définit la nature de la donnée qu’elle peut contenir (comme un nombre, un texte, ou encore un objet).
Cela nous permet de manipuler et utiliser ces données facilement tout au long de notre programme.

• La première de ces variables, que nous appelons simplement c représente chaque cellule que nous allons analyser dans notre sélection. Nous avons défini cette variable comme de type Range, ce qui signifie qu’elle peut faire référence à une cellule ou un ensemble de cellules dans notre feuille Excel. En utilisant c dans notre code, nous pourrons vérifier chaque cellule individuellement dans la plage de sélection et appliquer les actions souhaitées.
• Valeur est une autre variable, de type Variant, qui stockera la valeur contenue dans chaque cellule que nous parcourons. Variant est un type flexible qui peut contenir des nombres, du texte, ou même des cellules vides. Ici, Valeur nous permet d’examiner le contenu de chaque cellule et de vérifier si cette valeur apparaît plusieurs fois dans notre sélection.
• Dictionnaire est un objet spécial appelé "dictionnaire", qui fonctionne comme une sorte de liste ou de tableau avancé, mais qui nous permet d’associer chaque élément (appelé une "clé") à une valeur spécifique. Dans notre macro, le dictionnaire nous servira à stocker chaque valeur de cellule et le nombre de fois qu’elle apparaît dans la sélection. En assignant chaque valeur comme "clé"dans le dictionnaire et en associant cette clé à un nombre (le compteur d’occurrences), nous pouvons facilement suivre combien de fois chaque valeur est présente. Cela simplifie énormément notre tâche, car chaque fois qu’une valeur est rencontrée, nous vérifions dans le dictionnaire si elle y est déjà. Si c’est le cas, nous augmentons le compteur associé, sinon, nous l’ajoutons avec un compteur initial de 1.
• Enfin, CouleurIndex est un compteur de type Integer (nombre entier) que nous utilisons pour attribuer une couleur unique pour chaque doublon détecté. Excel dispose de plusieurs couleurs intégrées que l'on peut appliquer avec des indices de 1 à 56. Cela nous permet de définir une couleur différente pour chaque valeur dupliquée, en utilisant les indices de couleur disponibles dans Excel.

Dim c As Range
Dim Valeur As Variant
Dim Dictionnaire As Object
Dim CouleurIndex As Integer

Ensuite, nous créons notre dictionnaire en utilisant CreateObject("Scripting.Dictionary"). Comme nous venons de le voir, un dictionnaire est une sorte de tableau intelligent où chaque élément est stocké avec une clé unique. Dans notre cas, la clé sera la valeur de la cellule, et la valeur associée sera le nombre de fois que cette clé apparaît.

Cela nous permet de compter les occurrences de chaque valeur.

Set Dictionnaire =  CreateObject("Scripting.Dictionary")

Nous fixons l’index de couleur de base à 3. Chaque couleur dans Excel a un index, et nous démarrons ici à 3 pour éviter les couleurs de base (1 et 2). CouleurIndex sera ensuite incrémenté à chaque nouvelle couleur unique.

CouleurIndex = 3

Maintenant, nous allons pouvoir créer une première boucle afin de parcourir chaque cellule de la sélection et compter les occurrences de chaque valeur présente.

Pour mettre en place la boucle, nous utilisons la syntaxe For Each… In… Next, qui est une structure de boucle en VBA.

Cette syntaxe signifie que nous allons parcourir chaque élément d’une collection.

Ici, la "collection" est Selection, soit les cellules que nous avons sélectionnées avant de lancer la macro.

À chaque passage dans la boucle, c représente une cellule individuelle dans cette sélection.

Cette syntaxe est particulièrement utile, car elle s’adapte automatiquement à la taille de la sélection, qu’elle contienne 10, 100 ou 1000 cellules.

For Each c In Selection
Next

Nous fermons ensuite la boucle avec la ligne Next.

La macro revient alors au début de la boucle pour traiter la cellule suivante dans la sélection jusqu’à ce que toutes les cellules aient été parcourues.

À la fin de cette boucle, notre dictionnaire contiendra chaque valeur unique rencontrée dans la sélection, avec le nombre total de fois où chaque valeur apparaît.

Ensuite, nous vérifions si la cellule c n'est pas vide avant d'effectuer toute autre action.

Pour cela, nous utilisons la fonction VBA IsEmpty, qui renvoie True si la cellule est vide, et False si elle contient une valeur.

Cette vérification est importante, car nous ne voulons pas traiter les cellules vides ; elles ne représentent pas de doublons intéressants à analyser.

If IsEmpty(c.Value) = False Then
End If

Maintenant, nous allons vérifier si la valeur contenue dans la cellule c existe déjà dans notre dictionnaire.

Pour cela, nous utilisons la méthode .exists de l’objet Dictionnaire.

Le dictionnaire est comme une liste où chaque valeur de cellule est associée à une clé unique.

.exists prend alors la valeur de c en argument (soit c.Value) et renvoie True si cette clé est déjà présente dans le dictionnaire, et False si elle ne l’est pas encore.

If Dictionnaire.exists(c.Value) Then

Si la clé existe déjà, cela signifie que la valeur a été rencontrée au moins une fois auparavant dans notre sélection.

Nous augmentons le compteur correspondant de 1 pour indiquer que nous avons rencontré cette valeur une fois de plus.

Dictionnaire(c.Value) = Dictionnaire(c.Value) + 1

Si elle n'existe pas, c'est la première fois que nous voyons cette valeur, et nous allons donc l’ajouter au dictionnaire avec un compteur initial à 1 :

Else
    Dictionnaire.Add c.Value, 1
End If

Maintenant, nous allons mettre en place une deuxième boucle pour appliquer une couleur unique à chaque valeur qui apparaît au moins deux fois dans la sélection.

Nous nous basons sur le dictionnaire que nous avons précédemment rempli, qui contient chaque valeur de la sélection avec son nombre d’occurrences.

Ainsi, nous savons si une valeur apparaît au moins deux fois (c'est-à-dire, qu'elle est un doublon) et si elle nécessite une coloration spécifique.

Comme dans la première boucle, nous utilisons la syntaxe For Each… In… Next pour parcourir chaque cellule c dans la sélection.

Cela nous permet de vérifier une par une les valeurs des cellules que nous avons sélectionnées, et ainsi de contrôler si elles doivent recevoir une couleur spécifique en fonction de leur occurrence.

Nous vérifions également à nouveau si la cellule c n'est pas vide.

For Each c In Selection
    If Not IsEmpty(c.Value) = False Then
    End If
Next

Pour déterminer si nous devons colorer la cellule, nous mettons en place la condition If suivante :

If Dictionnaire(c.Value) >= 2 And CouleurIndex  <= 56 Then
End If

Dans cette condition, nous vérifions deux éléments importants :

• Dictionnaire(c.Value) >= 2 : Nous consultons le dictionnaire pour voir combien de fois c.Value apparaît dans la sélection. Si cette valeur est supérieure ou égale à 2, cela signifie qu'il s'agit d'un doublon, et nous devons lui attribuer une couleur. En n’appliquant des couleurs qu’aux valeurs ayant un nombre d’occurrences supérieur ou égal à 2, nous évitons de colorier les valeurs uniques.
• CouleurIndex <= 56 : Excel dispose d'un nombre limité de couleurs prédéfinies, représentées par des indices allant de 1 à 56. Pour éviter de dépasser cette limite, nous ajoutons cette condition. Si CouleurIndex dépasse 56, nous n'appliquons plus de couleurs supplémentaires, car Excel ne gère pas d'index supérieur à 56 pour les couleurs standards.

Si ces deux conditions sont remplies, nous passons aux étapes suivantes pour appliquer une couleur.

Pour cela, nous utilisons une clé spéciale appelée "Couleur" & c.Value pour vérifier si la valeur c.Value a déjà reçu une couleur unique.

En concaténant "Couleur" avec la valeur, nous créons une nouvelle clé dans le dictionnaire qui est spécifique à la couleur de cette valeur, comme "Couleur50" si la valeur est 50.

Si cette clé n'existe pas encore, cela signifie que nous n'avons pas encore assigné de couleur à ce doublon spécifique.

Si elle existe déjà, nous n'avons pas besoin de lui attribuer une nouvelle couleur ; nous réutiliserons simplement celle déjà enregistrée.

If Dictionnaire.exists("Couleur" &  c.Value) = False Then
End If

Lorsque la valeur n’a pas encore de couleur assignée, nous l’ajoutons au dictionnaire avec la clé "Couleur" & c.Value et lui attribuons la couleur actuelle représentée par CouleurIndex.

En utilisant .Add, nous créons une nouvelle entrée dans le dictionnaire où "Couleur" & c.Value est la clé, et CouleurIndex est la valeur.

Nous incrémentons ensuite CouleurIndex de 1 pour passer à la couleur suivante pour la prochaine valeur doublon. Ainsi, chaque nouvelle valeur doublon reçoit une couleur unique, jusqu’à ce que nous atteignions la limite de 56.

Dictionnaire.Add "Couleur" & c.Value,  CouleurIndex
CouleurIndex = CouleurIndex + 1

Il ne reste plus qu’à appliquer la couleur à la cellule c.

Nous accédons au dictionnaire avec la clé "Couleur" & c.Value pour récupérer l’indice de couleur attribué à cette valeur particulière.

En assignant cet indice à c.Interior.ColorIndex, nous appliquons cette couleur de fond à la cellule c.

L'utilisation de Interior.ColorIndex est une manière d'appliquer une couleur de fond spécifique à une cellule en utilisant un indice de couleur standard Excel.

c.Interior.ColorIndex =  Dictionnaire("Couleur" & c.Value)

La boucle va maintenant se poursuivre jusqu'à ce que toutes les cellules aient été analysées et, si nécessaire, coloriées.

À la fin de la macro, nous libérons le dictionnaire pour éviter toute fuite de mémoire.

Set Dictionnaire = Nothing

4. Exécution et test de la macro

Maintenant que notre macro est prête, il est temps de la tester.

Avant de lancer la macro, nous devons choisir les cellules que nous voulons analyser.

Par exemple, nous pourrions sélectionner toutes les cellules de la colonne "Tâche" ou de la colonne "Opérateur" pour y repérer les doublons.

Ensuite, il ne reste plus qu’à cliquer sur le bouton que nous avons créé au début du tutoriel pour exécuter la macro.

La macro analyse alors chaque cellule de notre sélection, identifie les valeurs en double, et leur applique une couleur unique, en fonction de leur occurrence.

Prenons un moment pour vérifier que les couleurs ont bien été appliquées comme prévu :

• Les valeurs uniques : Les cellules contenant des valeurs uniques (celles qui n’apparaissent qu’une seule fois dans la sélection) ne doivent pas être colorées. Cela confirme que la macro n’a appliqué de couleur qu’aux valeurs de doublon.
• Les doublons : Chaque doublon doit avoir une couleur spécifique. Par exemple, si "Alice" apparaît plusieurs fois, toutes les cellules contenant "Alice" doivent être de la même couleur, tandis qu’une autre valeur en double, comme "Bob", aura une couleur différente.