Comment surligner la ligne et/ou la colonne active automatiquement sur Excel ?

Dans ce tutoriel, je vais vous montrer comment surligner en une seule fois les lignes et/ou les colonnes de toutes les cellules sélectionnées sans perdre les mises en forme conditionnelles éventuellement déjà mises en place sur votre feuille de calcul Excel.

Pour cela, nous reviendrons sur un tutoriel précédent dans lequel nous avions introduit les concepts de base autour des mises en forme conditionnelles et des macros évènementielles VBA.

Ensuite, nous verrons comment adapter cette macro pour surligner toutes les lignes et colonnes des cellules sélectionnées.

Évidemment, pour réaliser ces opérations, nous ferons attention de bien préserver l’intégrité des mises en forme et mises en forme conditionnelles.

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. Rappel du tutoriel précédent

Si vous vous rappelez bien, dans un tutoriel précédent nous avions eu l’occasion de découvrir une méthode pour surligner les cellules de la ligne sélectionnée sur Excel, tout en conservant l’intégrité du format appliqué sur les cellules, grâce à une astuce un peu spéciale.

Pour commencer, je vous propose de revenir rapidement sur cette méthode. Évidemment, si vous souhaitez obtenir plus de détail, je vous invite à aller consulter le tutoriel correspondant.

Ici, pour surligner toute la ligne dans laquelle se trouve la cellule sélectionnée, nous ajoutons une règle de mise en forme conditionnelle.

En effet, si nous avions choisi de modifier directement la couleur de fond des cellules, alors lorsque nous sélectionnerions une autre cellule, nous n’aurions pas pu remettre la couleur d’origine des cellules car celle-ci aura été effacée.

La difficulté à laquelle nous allons nous confronter sera ensuite de supprimer cette règle de mise en forme conditionnelle, tout en conservant les autres intactes. Pour cela, nous avions utilisé une petite astuce en injectant un commentaire directement dans la formule utilisée :

Ici, nous pourrions en effet nous contenter de la deuxième partie de la formule « LIGNE(A8)=13 ». En effet, la première partie nous retournera toujours la valeur VRAI, car nous nous demandons si une chaîne de caractères quelconque est bien différente du nombre « 0 ».

Mais, le fait d’insérer la chaîne « ligneActiveMFC » nous permettra ensuite d’analyser la règle de mise en forme conditionnelle pour la supprimer.

Ensuite, nous avons mis en place cette mise en forme conditionnelle directement dans une macro évènementielle de la feuille de calcul, c’est-à-dire à l’intérieur d’une instruction VBA qui va se lancer automatiquement lorsque quelque chose va se produire sur la feuille de calcul :

Ici, nous avons appelé l’évènement « SelectionChange » pour exécuter une suite de commandes dès que l’utilisateur va cliquer sur une cellule.

Dans cette macro, nous avons commencé par déclarer une variable de type Range appelée « zone » dans laquelle nous avons stocké la plage de cellules qui contient toute la région de cellules dans laquelle se trouve la cellule active. La région d’une cellule correspond à toutes les cellules qui lui sont adjacentes.

Chaque région est séparée par des cellules vides.

Dim zone As Range
Set zone = ActiveCell.CurrentRegion

Ensuite, nous avons déclaré une variable de type Integer appelée « i ». Celle-ci va nous permettre de stocker un compteur permettant de parcourir toutes les mises en forme conditionnelles des cellules de la feuille de calcul.

Pour passer en revue ces mises en forme conditionnelles, nous allons pouvoir mettre en place une boucle « For ».

Dim i As Integer
For i = 1 To Cells.FormatConditions.Count

Il ne reste plus qu’à vérifier si la formule de la condition de mise en forme conditionnelle actuelle contient le texte « ligneActiveMFC », comme nous l’avons vu un peu plus tôt.

Si c'est le cas, cela signifie que cette condition de mise en forme conditionnelle a été créée par cette macro, et elle doit être supprimée avec la méthode « Delete ».

If Cells.FormatConditions(i).Formula1 Like  "ligneActiveMFC" Then
Cells.FormatConditions(i).Delete
End If

Maintenant que les règles de mise en forme conditionnelles ont bien été nettoyées, nous pouvons ajouter la nouvelle règle de mise en forme conditionnelle à la plage de cellules stockée un peu plus tôt dans la variable « zone ».

La règle de mise en forme conditionnelle repose sur la formule que nous avons vue un peu plus tôt, qui vérifie si la ligne de la cellule sélectionnée est égale à la ligne de chaque cellule dans la plage stockée dans la variable « zone ».

Enfin, nous affichons le texte des cellules pour lesquelles la condition est remplie en blanc avec une couleur de fond bleue.

With zone.FormatConditions.Add(xlExpression, Null,  "=(""ligneActiveMFC""<>0)*LIGNE("  & zone.Cells(1).Address(False, False) & ")=" &  ActiveCell.Row)
    .Font.ColorIndex = 2
    .Interior.ColorIndex = 32
End With

2. Surligner plusieurs lignes

Maintenant que nous venons de revoir comment fonctionne la macro de surlignement de la ligne dans laquelle se trouve la cellule active, voyons comment la modifier pour pourvoir surligne plusieurs lignes en même temps.

En effet, si nous sélectionnons une plage de plusieurs lignes, alors seule la première d’entre elles sera surlignée :

Cela vient du fonctionnement même de la macro.

En effet, plutôt que d’appliquer une mise en forme conditionnelle sur l’ensemble des cellules de la région dans laquelle se trouve la cellule active, nous allons utiliser une formule qui renverra tout le temps « VRAI », mais laquelle sera appliquée uniquement sur les cellules des lignes sélectionnées.

Pour commencer, nous allons modifier la formule de la règle de mise en forme conditionnelle pour ne conserver que la première partie.

La ligne :

With zone.FormatConditions.Add(xlExpression, Null,  "=(""ligneActiveMFC""<>0)*LIGNE("  & zone.Cells(1).Address(False, False) & ")=" &  ActiveCell.Row)

Devient donc :

With zone.FormatConditions.Add(xlExpression, Null,  "=(""ligneActiveMFC""<>0)")

Maintenant, si nous sélectionnons une cellule d’un tableau, c’est toute la région qui va être modifiée :

Il va donc nous rester à limiter l’étendu de la variable « zone » pour ne prendre en compte que la ligne de la cellule sélectionnée, que nous pouvons obtenir avec la variable « Target » de l’évènement « SelectionChange ».

Nous utiliserons ensuite l’instruction « EntireRow » sur cette variable pour étendre la plage sur tout la ligne.

Nous modifions donc la ligne d’affectation de la variable « zone » :

    Set zone = Target.EntireRow

Seulement, comme vous pouvez le constater, ce sont littéralement toutes les cellules de la ligne qui vont être impactées, que celles-ci appartiennent au tableau ou non :

Pour limiter les cellules modifiées à la région en cours, nous allons pouvoir utiliser la méthode « Intersect » de VBA.

Celle-ci permet en effet d’obtenir la plage des cellules situées à l’intersection de deux plages de cellules.

Pour obtenir toutes les cellules situées sur la ligne active, et à l’intérieur du tableau nous allons donc utiliser la ligne de code :

    Set zone = Intersect(Target.EntireRow,  Target.CurrentRegion)

« CurrentRegion » permet en effet, comme nous l’avons vu un peu tôt d’obtenir la plage des cellules qui correspond à la zone active d’une cellule donnée. Cela revient donc au même résultat que si nous avions appuyé sur les touches [Ctrl]+[A] après avoir sélectionné une cellule du tableau.

Maintenant, si nous sélectionnons une cellule du tableau, seules les cellules de la ligne située à l’intérieur de ce tableau vont passer en bleu :

3. Surligner les colonnes

À présent, nous allons découvrir quelques petites modifications à mettre en place pour pouvoir surligner également la colonne dans laquelle se trouve la ou les cellules sélectionnées.

Pour commencer, nous allons déplacer la ligne d’affectation de la variable « zone » juste avant la définition de la règle de création de la mise en forme conditionnelle :

    Set zone = Intersect(Target.EntireRow,  Target.CurrentRegion)
    With zone.FormatConditions.Add(xlExpression, Null,  "=(""ligneActiveMFC""<>0)")
        .Font.ColorIndex = 2
        .Interior.ColorIndex = 32
    End With

De cette manière, nous allons pouvoir effectuer un rapide copier-coller de ces lignes, dans lesquelles nous n’aurons plus qu’à modifier les cellules de la variable « zone », afin que celles-ci soient dérivées de la colonne dans laquelle se trouve la ou les cellules sélectionnées, toujours à l’intérieur de la région active :

    Set zone = Intersect(Target.EntireColumn,  Target.CurrentRegion)
    With zone.FormatConditions.Add(xlExpression, Null,  "=(""ligneActiveMFC""<>0)")
        .Font.ColorIndex = 2
        .Interior.ColorIndex = 41
    End With

Nous appliquons ici une couleur de fond un peu plus claire :

Par contre, si nous sélectionnons plusieurs cellules les unes après les autres, nous allons être confrontés à une erreur présentée par VBA :

Cette erreur est liée à un bug dans la boucle qui permet de supprimer les mises en forme conditionnelles avec l’identifiant « ligneActiveMFC ».

Pour corriger ce bug, nous allons modifier le sens dans lequel nous passons les règles en revue, en commençant par dont l’index correspond au nombre de mises en forme appliquées pour finir sur l’index « 1 » :

    For i = Cells.FormatConditions.Count To 1 Step -1
        If Cells.FormatConditions(i).Formula1 Like  "*ligneActiveMFC*" Then
            Cells.FormatConditions(i).Delete
        End If
    Next