Passer au contenu principal

Comment vlookup et renvoyer la couleur d'arrière-plan avec la valeur de recherche dans Excel?

Auteur : Siluvia Dernière modification: 2020-06-18

Supposons que vous ayez un tableau comme ci-dessous. Vous voulez maintenant vérifier si une valeur spécifiée se trouve dans la colonne A, puis renvoyer la valeur correspondante avec la couleur d'arrière-plan dans la colonne C. Comment y parvenir? La méthode décrite dans l'article peut vous aider à résoudre le problème.

Vlookup et retourne la couleur d'arrière-plan avec la valeur de recherche par la fonction définie par l'utilisateur


Vlookup et retourne la couleur d'arrière-plan avec la valeur de recherche par la fonction définie par l'utilisateur

Veuillez procéder comme suit pour rechercher une valeur et renvoyer sa valeur correspondante avec la couleur d'arrière-plan dans Excel.

1. Dans la feuille de calcul contient la valeur que vous souhaitez rechercher, cliquez avec le bouton droit sur l'onglet de la feuille et sélectionnez Voir le code dans le menu contextuel. Voir la capture d'écran:

2. Dans l'ouverture Microsoft Visual Basic pour applications , veuillez copier ci-dessous le code VBA dans la fenêtre Code.

Code VBA 1: Vlookup et retourne la couleur d'arrière-plan avec la valeur de recherche

Sub Worksheet_Change(ByVal Target As Range)
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3. Puis clique insérer > Module, et copiez le code VBA 2 ci-dessous dans la fenêtre Module.

Code VBA 2: Vlookup et retourne la couleur d'arrière-plan avec la valeur de recherche

Public xDic As New Dictionary
Function LookupKeepColor (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepColor = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepColor = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. Après avoir inséré les deux codes, cliquez sur Outils > Bibliographie. Puis vérifiez le Exécution de scripts Microsoft boîte dans la Références - VBAProject boite de dialogue. Voir la capture d'écran:

5. appuie sur le autre + Q touches pour quitter Microsoft Visual Basic pour applications et revenez à la feuille de calcul.

6. Sélectionnez une cellule vide à côté de la valeur de recherche, puis entrez la formule =LookupKeepColor(E2,$A$1:$C$8,3) dans la barre de formule, puis appuyez sur la touche Entrée.

Notes: Dans la formule, E2 contient la valeur que vous rechercherez, 1 $ AU: 8 $ C $ est la plage de table et le nombre 3 signifie que la valeur correspondante que vous retournerez se situe dans la troisième colonne du tableau. Veuillez les changer selon vos besoins.

7. Continuez à sélectionner la première cellule de résultat et faites glisser la poignée de remplissage vers le bas pour obtenir tous les résultats avec leur couleur d'arrière-plan. Voir la capture d'écran.


Articles Liés:

Meilleurs outils de productivité bureautique

🤖 Aide à l'IA Kutools: Révolutionner l'analyse des données en s'appuyant sur : Exécution intelligente   |  Générer du code  |  Créer des formules personnalisées  |  Analyser les données et générer des graphiques  |  Invoquer les fonctions Kutools...
Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

Description


Office Tab apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
Comments (34)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
This code is working fine , except in the cells that the formula is inputed in bring up 0 when the cell it is looking up is blank , my question is how do i make it ignore blank cells and prevent the cell the formula is in from inputting a 0 , is there some wheres in the code to enter an =IFERROR function maybe ?
This comment was minimized by the moderator on the site
Hi Kyle,

I tested this code and it does not bring up 0 when the cell it looks for is blank.
Perhaps you could include the formula in the IF function, as shown below, to prevent returning a result of 0.
=IF(B2="","",LookupKeepColor(E2,$A$1:$C$8,3))
This comment was minimized by the moderator on the site
Hi, Is this code working for office 2016 and later versions ?
This comment was minimized by the moderator on the site
no its not returning color.
This comment was minimized by the moderator on the site
I use times and dates from excel reports to create timesheets for our employees. If the specified date, for example, 2020/08/11 matches the date on the next tabs array (which contains many cells with the same date but different times) I want it to pull only the cell filled in orange which will be stated as 2020/08/11 7:45. Is this possible?
This comment was minimized by the moderator on the site
How I do
This comment was minimized by the moderator on the site
Hello, i have a sheet with 10,948 rows, its taking some time to pull the information with colors, still waiting. Is this normal, or there is something wrong?
This comment was minimized by the moderator on the site
This was AWESOME! followed the steps and it works beautifully! Thank you!
This comment was minimized by the moderator on the site
I have many records, it takes too long to process, and the code keeps on running even after completion. Please help
This comment was minimized by the moderator on the site
I have used this in Excel 2016 and only the data is transferred from Source to Target...….color is not transferred. Thoughts on what issue might be: Is it incompatibility with Excel 2016? Thanks. MT
This comment was minimized by the moderator on the site
i am getting the required cell color but i also need the lookup value as it is returning integer instead of string
This comment was minimized by the moderator on the site
Hi, Can i apply vlookup on color cells with no data in them
This comment was minimized by the moderator on the site
this works fine in office 2010, but not the 2013 version. Is there an update to the macro?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations