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

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

Incroyable! Utilisation d'onglets efficaces dans Excel comme Chrome, Firefox et Safari!

Économisez 50% de votre temps et réduisez chaque jour des milliers de clics de souris!

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 > Références. 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.

Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.

Lire la suite... Téléchargement Gratuit... Achat...

Office Tab apporte une interface à onglets à Office et simplifie considérablement 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!

Lire la suite... Téléchargement Gratuit... Achat...

31 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
mohamed · 1 years ago
Hi, Is this code working for office 2016 and later versions ?
- Reply
- To post as a guest, your comment is unpublished.
  gouravhere · 10 months ago
  
  no its not returning color.
  
  Reply
To post as a guest, your comment is unpublished.
Tundra · 1 years ago
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?
- Reply
To post as a guest, your comment is unpublished.
andi · 1 years ago
How I do
- Reply
To post as a guest, your comment is unpublished.
Evord · 1 years ago
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?
- Reply
To post as a guest, your comment is unpublished.
AK · 1 years ago
This was AWESOME! followed the steps and it works beautifully! Thank you!
- Reply
- To post as a guest, your comment is unpublished.
  Evord · 1 years ago
  
  I have many records, it takes too long to process, and the code keeps on running even after completion. Please help
  
  Reply
To post as a guest, your comment is unpublished.
Mike · 1 years ago
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
- Reply
To post as a guest, your comment is unpublished.
Mayank Sharma · 1 years ago
i am getting the required cell color but i also need the lookup value as it is returning integer instead of string
- Reply
To post as a guest, your comment is unpublished.
Prashant · 2 years ago
Hi, Can i apply vlookup on color cells with no data in them
- Reply
To post as a guest, your comment is unpublished.
Richard S · 2 years ago
this works fine in office 2010, but not the 2013 version. Is there an update to the macro?
- Reply
To post as a guest, your comment is unpublished.
johnrojas944@gmail.com · 2 years ago
good afternoon bob to these codes you can change them in addition to the color call me the same color format and font that contains the cell

Thank you
- Reply
To post as a guest, your comment is unpublished.
Ari · 2 years ago
Is there any way to modify this to use as an Hlookup?
- Reply
To post as a guest, your comment is unpublished.
JonB22 · 2 years ago
I am returning a value, but not getting the color. used the sheet to sheet code, followed to a T. Any ideas on why I am not getting the color?
- Reply
To post as a guest, your comment is unpublished.
Ela · 2 years ago
When I open the View Code window, there is a window but is not empty. Can I paste the code under the text that is already there or how do I open a new "blank page" please?
- Reply
To post as a guest, your comment is unpublished.
kmoen · 2 years ago
I have windows for Mac , when I get to Step 4 - there is no option for Microsoft Scripting Runtime, is there something else I should be selecting?
- Reply
To post as a guest, your comment is unpublished.
asfanayaz@gmail.com · 3 years ago
this code is running on same sheet but how can I lookup cell color from one sheet to another sheet in excel
Thanks in advance :)
- Reply
- To post as a guest, your comment is unpublished.
  Bob · 3 years ago
  
  Use this slight modification of the code posted.
  
  Public xDic As New Dictionary
  Public strWB As String
  Public strWS As String
  
  Function CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
  Dim xFindCell As Range
  On Error Resume Next
  
  strWB = LookupRng.Parent.Parent.Name '*** Remember the Workbook where the data and color are coming from
  strWS = LookupRng.Parent.Name '*** Remember the Worksheet where the data and color are coming from
  
  Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
  
  If xFindCell Is Nothing Then
  CLookup = ""
  xDic.Add Application.Caller.Address, ""
  Else
  CLookup = xFindCell.Offset(0, xCol - 1).Value
  xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
  
  End If
  End Function
  
  Sub Worksheet_Change(ByVal Target As Range)
  Dim I As Long
  Dim xKeys As Long
  Dim xDicStr As String
  Dim rngLoc As Range
  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 = Application.Workbooks(strWB).Worksheets(strWS).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
  
  Reply
To post as a guest, your comment is unpublished.
Asfan · 3 years ago
this code working on same sheet, how can i look up color from one sheet to another?
- Reply
- To post as a guest, your comment is unpublished.
  Bob · 3 years ago
  
  Use this slight modification of the code posted.
  
  Public xDic As New Dictionary
  Public strWB As String
  Public strWS As String
  
  Function CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
  Dim xFindCell As Range
  On Error Resume Next
  
  strWB = LookupRng.Parent.Parent.Name '*** Remember the Workbook where the data and color are coming from
  strWS = LookupRng.Parent.Name '*** Remember the Worksheet where the data and color are coming from
  
  Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
  
  If xFindCell Is Nothing Then
  CLookup = ""
  xDic.Add Application.Caller.Address, ""
  Else
  CLookup = xFindCell.Offset(0, xCol - 1).Value
  xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
  
  End If
  End Function
  
  Sub Worksheet_Change(ByVal Target As Range)
  Dim I As Long
  Dim xKeys As Long
  Dim xDicStr As String
  Dim rngLoc As Range
  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 = Application.Workbooks(strWB).Worksheets(strWS).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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Ana · 2 years ago
  
  Hello Bob! The code works, however, for some reason it copies values from Sheet 2 to Sheet 1, but copies the cell formatting and leaves it in Sheet 2... It's hard to explain, but it basically splits one action (copy text + copy formation and paste it into the cell) into two. Do you know how to make it to do both on one sheet? Thank you!
  
  Reply
To post as a guest, your comment is unpublished.
Brad Lehoux · 3 years ago
I would also like to VLOOKUP on sheet 2 and extract the data and background color from sheet 1
- Reply
- To post as a guest, your comment is unpublished.
  Bob · 3 years ago
  
  Use this slight modification of the code posted.
  
  Public xDic As New Dictionary
  Public strWB As String
  Public strWS As String
  
  Function CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
  Dim xFindCell As Range
  On Error Resume Next
  
  strWB = LookupRng.Parent.Parent.Name '*** Remember the Workbook where the data and color are coming from
  strWS = LookupRng.Parent.Name '*** Remember the Worksheet where the data and color are coming from
  
  Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
  
  If xFindCell Is Nothing Then
  CLookup = ""
  xDic.Add Application.Caller.Address, ""
  Else
  CLookup = xFindCell.Offset(0, xCol - 1).Value
  xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
  
  End If
  End Function
  
  Sub Worksheet_Change(ByVal Target As Range)
  Dim I As Long
  Dim xKeys As Long
  Dim xDicStr As String
  Dim rngLoc As Range
  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 = Application.Workbooks(strWB).Worksheets(strWS).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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Jennifer · 2 years ago
  
  It this to fix an error in the original code or is this to allow it to look up from a different sheet?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Bob · 2 years ago
  
  This change to the original code allows you to do the vlookup w/color from one Worksheet to another or from one Workbook to another. But this code needs to be placed in the TARGET worksheet rather than the SOURCE worksheet as was described in the original code. That's because the original code only worked in one Worksheet, so it was both the Source and the Target. This is not a fix to the original code. I just added code to allow you to pull from any Workbook/Worksheet (Source) into your Worksheet (Target). The original code worked as the programmer intended.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  alan · 2 years ago
  
  hello I didi it the procedure but i cant bring the background color in the new worksheet , i have a doubt if i put in correct way the comand strWB and strWS i puted this strWB = LookupRng.Reporte_Opcionales
  strWS = LookupRng.Imprimir Reporte_Opcionales is the name of my workbook
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Bob · 2 years ago
  
  I believe the lines are supposed to be the following (EXACTLY):
  
  strWB = LookupRng.Parent.Parent.Name
  
  strWS = LookupRng.Parent.Name
  
  I came up with this about 4 months ago so I don't remember exactly how I came up with this, but you weren't supposed to replace this code with anything else.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Sergio Salazar Brice · 2 years ago
  
  Bob, help me plese , colud you please check the code? im sure that you can fix it because it does brigme the background color from other sheet .
  
  by the way the code that is for work in the same sheet work but i need bring the data from other sheet :(.
  
  thanks in advance
  greetengs from Monterrey México.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  alansystem@hotmail.c · 2 years ago
  
  what the name in strWB has repeated Parent.Parent ???? does that correct?
  thanks in advance.
  
  Reply
To post as a guest, your comment is unpublished.
Sander Viet · 3 years ago
How do I change this code, in order for it to extract the background color from another sheet?
For example, I would like to use a VLOOKUP in Sheet 2, which extracts the data and background color from Sheet 1.
- Reply
- To post as a guest, your comment is unpublished.
  Jim · 3 years ago
  
  I have this exact same question! Any advice would be greatly appreciated.
  
  Reply

Connectez-vous à votre compte

Réinitialiser mot de passe