[VIDEO]Comment surligner la ligne de la cellule sélectionnée

Dans ce nouveau tutoriel, nous allons répondre à une question qui revient souvent : comment mettre en évidence la ligne dans laquelle se trouve la cellule sélectionnée. Nous allons voir deux méthodes qui vont permettent d’arriver au même résultat attendu. Ces deux techniques avancées, ont l’avantage de préserver la mise en forme d’origine du classeur lorsqu’une nouvelle ligne est sélectionnée

Téléchargement

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

Tutoriel Vidéo

Partie 1 :

Partie 2 :

1. Première méthode

Cette première méthode est la plus simple à mettre en place. Elle va consister à stocker le numéro de la ligne sélectionnée, puis d’appliquer une mise en forme conditionnelle sur la ligne concernée.

Nous allons donc procéder de la manière suivante :

• Nous sélectionnons une cellule vide du classeur, en cliquant simplement dessus (par exemple la cellule située aux coordonnées « G1 », comme le montre l’illustration ci-dessous),

• Puis nous lui donnons un nom simple, que nous réutiliserons par la suite. Pour donner un nom à une cellule, il suffit de le saisir dans la zone de nom en haut à gauche. Ne pas oublier de valider en appuyant sur la touche [Entrée] du clavier. Dans notre exemple, donnons le noms « _maLigne » à la cellule « G1 »,

• Pour l’exemple, nous allons donner le numéro d’une ligne comme valeur pour cette cellule (nous verrons juste après comment modifier automatiquement la valeur de la cellule). Par exemple la ligne 6

À présent, nous disposons d’une cellule que nous pouvons appeler simplement (« =_maLigne ») et qui contient le numéro de la ligne que nous souhaitons mettre en surbrillance.

Pour ce faire, nous allons utiliser la fonctionnalité de mise-en-forme conditionnelle d’Excel, qui comme son nom l’indique clairement permet de personnaliser les paramètres de mise-en-forme d’une ou plusieurs cellules lorsqu’une ou plusieurs conditions sont effectivement remplies :

• Commençons par sélectionner les cellules sur lesquelles se trouve les lignes que nous souhaitons surligner. Pour cela, il suffit de placer le curseur sur la première d’entre elles, puis de faire glisser la souris jusqu’à sélectionner toutes les cellules

• Pour sélectionner plusieurs groupes de cellules (par exemple pour sélectionner nos quatre tableaux), il suffit de garder la touche [Ctrl] du clavier en foncée, puis de sélectionner chacune des différentes zones, sans relâcher ce dernier

• Pour insérer la mise-en-forme conditionnelle, rendez-vous dans l’onglet Accueil du ruban, puis cliquez sur Mise en forme conditionnelle > Nouvelle règle

• Dans la fenêtre qui s’affiche, choisissez l’option « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué »

• Puis saisissez la formule suivante, comme le montre l’illustration ci-dessus : « =LIGNE(E18)=_maLigne ». Remplacez les coordonnées de la cellule par celles de la cellule sélectionnée (vous retrouverez ces coordonnées dans la zone de nom en haut à gauche),
• Puis cliquez sur le bouton Format… afin de personnaliser la mise en forme que nous souhaitons appliquer, en choisissant par exemple un fond de couleur bleu foncé et une police de caractères blanche :

• Enfin, vous pouvez valider en appuyant sur les deux boutons [OK] successifs

À présent, vous pourrez constater que la ligne dont le numéro correspond à la valeur de la cellule _maLigne possède un fond de couleur bleu

Changez la valeur de cette cellule et la ligne en surbrillance va elle aussi être modifiée :

La dernière opération à mettre en place pour cette première méthode va consister à modifier automatiquement la valeur contenue dans la cellule « _maLigne » avec le numéro de la ligne contenant la cellule sélectionnée.

Pour cela, nous allons insérer un petit bout de macro-commande en VBA dans le code de la feuille de calcul :

• Commençons par effectuer un clic-droit sur l’onglet de la feuille de calcul, afin de sélectionner l’option « Visualiser le code » :

• Cela permet d’ouvrir l’outil de développement des macro-commandes d’Excel (VBA) directement sur le code de la feuille de calcul (attention, le code que nous allons saisir ne sera accessible que depuis cette feuille de calcul)

• La commande que nous allons saisir devra être lancée automatiquement par Excel à chaque fois que l’utilisateur va sélectionner une cellule. Nous allons donc insérer notre code à l’intérieur d’une procédure évènementielle
• Dans le menu déroulant situé au-dessus de la zone de saisie du code, nous allons sélectionner « Worksheet », puis dans le second menu « SelectionChange »

• Excel va allors aujouter automatiquement une procédure portant le nom de « Worksheet_SelectionChange(ByVal Target As Range) ». Il s’agit d’un évènement qui sera lancé à chaque fois qu’une nouvelle cellule va être sélectionnée. La cellule sélectionnée va être retournée dans la variable « Target ».
• Pour insérer le numéro de la ligne sélectionnée dans la cellule _maLigne, nous allons uniquement insérer la ligne de code suivante dans cette procédure :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("_maLigne") = ActiveCell.Row
End Sub

Vous pouvez maintenant tester de sélectionner une cellule pour constater que la ligne en surbrillance va maintenant s’adapter à la cellule sélectionnée.

2. Seconde méthode « Full VBA »

Dans cette seconde méthode, l’ensemble de la logique va être intégrée dans la procédure évènementielle de VBA afin de pouvoir s’adapter à tous les cas de figure auxquels nous allons pouvoir être confronté.

Pour commencer, nous allons supprimer les éléments que nous avons mis en place dans la première partie :

• Suppression de la cellule _maLigne : sélectionnez la cellule, puis à l’aide du menu contextuel qui s’affiche lorsque l’on effectue un clic-droit sur celle-ci, choisissez « Supprimer… » :

• Dans la fenêtre suivante, optez pour l’option « Décaler les cellules vers la gauche », puis validez en cliquant sur le bouton [OK] :

• Suppression de la mise-en-forme conditionnelle : Dans menu Accueil du ruban, rendez-vous dans « Mise en forme conditionnelle » > « Gérer les règles… »

• Dans la fenêtre qui s’affiche, choisissez « Cette feuille de calcul », puis sélectionnez la règle et cliquez sur le bouton « Supprimer la règle » :

À présent, nous allons modifier l’événement « Worksheet_SelectionChange » afin de modifier automatiquement la ligne à modifier. Pour en savoir plus sur cette procédure particulière de VBA, je vous invite à retourner dans la première partie de cet article.

Tout d’abord, nous allons vouloir supprimer toute mise-en-forme conditionnelle déjà définie, afin d’éviter que celles-ci ne se rajoutent indéfiniment à chaque fois que nous allons cliquer sur de nouvelles cellules. Pour cela nous allons utiliser l’instruction Delete de FormatConditions :

    Cells.FormatConditions.Delete

Comme vous pouvez le voir, nous appliquons cette instruction sur l’ensemble des cellules de la feuille de calcul (« Cells »).

Puis nous allons ajouter une nouvelle mise-en-forme conditionnelle :

Private Sub Worksheet_SelectionChange(ByVal Target As Range
    Cells.FormatConditions.Delete
     With ActiveCell.CurrentRegion.FormatConditions.Add(Type:=xlExpression, Formula1:="=LIGNE(" & ActiveCell.CurrentRegion.Cells(1).Address(False, False) & ")=" & ActiveCell.Row) '1
        .Font.ColorIndex = 2        ' 2
        .Interior.ColorIndex = 32   ' 3
    End With    '4 
End Sub

Ces lignes se décomposent de la manière suivante :

• 1 : Dans cette première ligne, nous ajoutons une nouvelle mise-en-forme conditionnelle dans la pile des mise-en-formes conditionnelles de la feuille de calcul (qui est vide vu que nous venons juste de supprimer toute mise-en-forme conditionnelle existante).
• Cette mise-en-forme conditionnelle est à insérer dans le tableau dans lequel se trouve la cellule sélectionnée (ActiveCell.CurrentRegion), ce qui permet de ne sélectionner la ligne qu’à l’intérieur de ce dernier. Nous choisissons d’activer la mise en forme conditionnelle lorsque que la formule suivante renvoie pour valeur « VRAI » : « =LIGNE(" & ActiveCell.CurrentRegion.Cells(1).Address(False, False) & ")=" & ActiveCell.Row ». Il s’agit de la même formule que celle que nous avions saisie dans la première partie de cet article, mais rendue dynamique grâce à l’utilisation de variables : « ActiveCell.CurrentRegion.Cells(1).Address(False, False) » : il s’agit ici de récupérer les coordonnées de la première cellule du tableau sélectionné (« CurrentRegion.Cells(1) »).
• Attention nous souhaitons que cette mise-en-forme conditionnelle s’applique à l’ensemble des cellules de la région en cours, nous devons donc récupérer les coordonnées relatives de cette cellule, d’où les paramètres « False » appliqués à l’instruction Address.
• Enfin, nous vérifions si chaque de ces cellule à le même numéro de ligne que la cellule sélectionnée (« =LIGNE(…)=ActiveCell.Row ».
• Vous noterez au passage que cette ligne commence par une instruction With, ce qui permet de définir directement un ensemble de paramètres à cette mise-en-forme conditionnelle
• 2 : Nous appliquons la couleur blanche aux textes,
• 3 : Ainsi qu’un fond de couleur bleu
• 4 : Enfin nous refermons le With

Et voilà , c’est tout !

Vous pouvez tester, et si vous avez suivi à la lettre le déroulement de cet article, les lignes de chacun de nos tableaux devraient passer en surbrillance lorsqu’une cellule est sélectionnée.

3. Technique avancée pour conserver les mises en forme conditionnelles déjà définies

Comme nous l’avons découvert dans la partie précédente de ce tutoriel, pour éviter qu’un grand nombre de règles de mise en forme conditionnelles ne se cumulent dans une feuille de calcul, nous purgeons celles qui pourraient déjà être définie en utilisant la ligne suivante :

Cells.FormatConditions.Delete

Ce qui permet de supprimer toute mise en forme existante au sein de la feuille de calculs !

Et c’est justement là le problème…

En effet, si nous avions déjà défini des règles de mise en forme conditionnelle, alors celles-ci seraient également supprimées, car cette astuce ne fait aucune distinction dans les mises en forme conditionnelles en supprimant les supprimant toutes !

Heureusement, nous allons maintenant découvrir une astuce qui va nous permettre d’identifier spécifiquement les mises en forme nous devons supprimer.

L’astuce que nous allons découvrir maintenant consiste à poser une étiquette sur la mise en forme conditionnelle afin de pouvoir l’identifier facilement et de ne supprimer que celle-ci !

Pour cela nous allons modifier la formule insérée dans le cadre de la mise en forme comme ceci :

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

La première partie consiste simplement à effectuer une comparaison Booléenne.

C’est-à-dire que nous demandons à VBA de regarder si la valeur saisie entre guillemets est différente du chiffre 0.

Bien évidemment cela sera toujours le cas, cette comparaison sera toujours égale à VRAI, ce qui fait que cette partie sera annulée lors du traitement de la formule !

Inutile dans ce cas ?

Et non, car cette astuce nous a permis de poser une étiquette d’identification sur la mise en forme conditionnelle.

Il nous suffira maintenant de rechercher le terme de l’étiquette.

Ici ce terme correspond à « ligneActiveMFC ».

Pour l’identifier, il va maintenant nous suffire de passer en revue toutes les mises en forme conditionnelles contenues dans la feuille et de ne supprimer que celles contenant ce terme !

Pour cela, nous utilisons bien entendu une boucle For :

    Dim i As Integer
    For i = 1 To Cells.FormatConditions.Count
        With Cells.FormatConditions(i)
            If .Formula1 Like "*ligneActiveMFC*"  Then
                .Delete
            End If
        End With
    Next

Donc :

• Nous commençons par instancier une variable i qui est un nombre entier (Integer),
• Cette variable nous permet de lancer une boucle For, avec une valeur de départ égale à 1, et pour valeur maximale le nombre de mise en conditionnelles déjà mise en place,
• Pour simplifier la rédaction de la macro, nous utilisons un With qui permet d’appliquer directement les méthodes et propriétés sur la mise en forme conditionnelle en cours d’analyse (Cells.FormatConditions(i)),
• Puis avec un test IF, nous regardons si la mise en forme conditionnelle courante contient l’étiquette en utilisant l’opérateur de comparaison LIKE avec les caractères joker étoiles (« * »),
• Et enfin, lorsque le test est vérifié, nous pouvons supprimer la mise en forme conditionnelle actuelle !

p>Il ne reste plus qu’à tester la macro !

Pour cela, nous sélectionnons une série de chiffres, puis nous insérons une mise en forme conditionnelle (menu Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Supérieur à…) :

Ce qui nous permet d’afficher en rouge toutes les cellules dont la valeur est supérieure à la moyenne des cellules sélectionnées :

Et maintenant, lorsque nous sélectionnons une cellule, comme par magie, la mise en forme conditionnelle que nous venons juste d’insérer reste toujours présente !

Article publié le 12 décembre 2018 et mis à jour le 6 avril 2020