Note: The other languages of the website are Google-translated. Back to English

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

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.


Articles Liés:


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éeplus
  • 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 gammesplus
  • 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 cellulesplus
  • 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 PDFplus
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2021 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

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!
bas de cabine
Commentaires (34)
Noté 5 hors 5 · évaluations 1
Ce commentaire a été minimisé par le modérateur sur le site
Comment changer ce code, afin qu'il extraie la couleur de fond d'une autre feuille ?
Par exemple, je voudrais utiliser un VLOOKUP dans la feuille 2, qui extrait les données et la couleur d'arrière-plan de la feuille 1.
Ce commentaire a été minimisé par le modérateur sur le site
J'ai exactement la même question! Tout avis serait grandement apprécié.
Ce commentaire a été minimisé par le modérateur sur le site
Je voudrais également VLOOKUP sur la feuille 2 et extraire les données et la couleur de fond de la feuille 1
Ce commentaire a été minimisé par le modérateur sur le site
Utilisez cette légère modification du code affiché.


Public xDic comme nouveau dictionnaire
Public strWB As String
Public strWS en tant que chaîne

Fonction CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Rappelez-vous le classeur d'où proviennent les données et la couleur
strWS = LookupRng.Parent.Name '*** Rappelez-vous la feuille de calcul d'où proviennent les données et la couleur

Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

Si xFindCell n'est rien alors
CRecherche = ""
xDic.Add Application.Caller.Address, ""
autre
CLookup = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

Si fin
Fonction de fin

Sub Worksheet_Change (ByVal Target As Range)
Dim I As Long
Dim xKeys aussi longtemps
Dim xDicStr As String
Dim rngLoc As Range
On Error Resume Next
Application.ScreenUpdating = Faux
xKeys = UBound(xDic.Keys)
Si xKeys >= 0 Alors
Pour I = 0 Vers UBound(xDic.Keys)
xDicStr = xDic.Items(I)
Si xDicStr <> "" Alors
Range(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
autre
Range(xDic.Keys(I)).Interior.Color = xlNone
Si fin
Suivant
Définir xDic = Rien
Si fin
Application.ScreenUpdating = True
End Sub
Ce commentaire a été minimisé par le modérateur sur le site
Est-ce pour corriger une erreur dans le code d'origine ou est-ce pour lui permettre de rechercher à partir d'une feuille différente?
Ce commentaire a été minimisé par le modérateur sur le site
Cette modification du code d'origine vous permet de faire le vlookup w/color d'une feuille de calcul à une autre ou d'un classeur à un autre. Mais ce code doit être placé dans la feuille de calcul CIBLE plutôt que dans la feuille de calcul SOURCE, comme décrit dans le code d'origine. C'est parce que le code d'origine ne fonctionnait que dans une seule feuille de calcul, c'était donc à la fois la source et la cible. Ce n'est pas un correctif du code d'origine. Je viens d'ajouter du code pour vous permettre d'extraire de n'importe quel classeur/feuille de calcul (source) dans votre feuille de calcul (cible). Le code original a fonctionné comme prévu par le programmeur.
Ce commentaire a été minimisé par le modérateur sur le site
bonjour j'ai fait la procédure mais je ne peux pas apporter la couleur de fond dans la nouvelle feuille de calcul, j'ai un doute si je mets correctement la commande strWB et strWS j'ai mis ce strWB = LookupRng.Reporte_Opcionales
strWS = LookupRng.Imprimir Reporte_Opcionales est le nom de mon classeur
Ce commentaire a été minimisé par le modérateur sur le site
Je crois que les lignes sont censées être les suivantes (EXACTEMENT):

strWB = LookupRng.Parent.Parent.Name

strWS = LookupRng.Parent.Name


J'ai trouvé cela il y a environ 4 mois, donc je ne me souviens pas exactement comment j'ai trouvé cela, mais vous n'étiez pas censé remplacer ce code par autre chose.
Ce commentaire a été minimisé par le modérateur sur le site
ce que le nom dans strWB a répété Parent.Parent ???? ça corrige ?
merci à l'avance.
Ce commentaire a été minimisé par le modérateur sur le site
Bob, aidez-moi s'il vous plait, merci de vérifier le code ? Je suis sûr que vous pouvez le réparer car il éclaire la couleur d'arrière-plan d'une autre feuille.

au fait, le code qui est pour le travail dans la même feuille fonctionne mais j'ai besoin d'apporter les données d'une autre feuille :(.

Merci d'avance
greetengs de Monterrey Mexique.
Ce commentaire a été minimisé par le modérateur sur le site
Cela fonctionne très bien, merci !
Noté 5 hors 5
Ce commentaire a été minimisé par le modérateur sur le site
ce code fonctionnant sur la même feuille, comment puis-je rechercher la couleur d'une feuille à l'autre?
Ce commentaire a été minimisé par le modérateur sur le site
Utilisez cette légère modification du code affiché.


Public xDic comme nouveau dictionnaire
Public strWB As String
Public strWS en tant que chaîne

Fonction CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Rappelez-vous le classeur d'où proviennent les données et la couleur
strWS = LookupRng.Parent.Name '*** Rappelez-vous la feuille de calcul d'où proviennent les données et la couleur

Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

Si xFindCell n'est rien alors
CRecherche = ""
xDic.Add Application.Caller.Address, ""
autre
CLookup = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

Si fin
Fonction de fin

Sub Worksheet_Change (ByVal Target As Range)
Dim I As Long
Dim xKeys aussi longtemps
Dim xDicStr As String
Dim rngLoc As Range
On Error Resume Next
Application.ScreenUpdating = Faux
xKeys = UBound(xDic.Keys)
Si xKeys >= 0 Alors
Pour I = 0 Vers UBound(xDic.Keys)
xDicStr = xDic.Items(I)
Si xDicStr <> "" Alors
Range(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
autre
Range(xDic.Keys(I)).Interior.Color = xlNone
Si fin
Suivant
Définir xDic = Rien
Si fin
Application.ScreenUpdating = True
End Sub
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour bob! Le code fonctionne, cependant, pour une raison quelconque, il copie les valeurs de la feuille 2 à la feuille 1, mais copie la mise en forme de la cellule et la laisse dans la feuille 2... C'est difficile à expliquer, mais il divise essentiellement une action (copier le texte + copier la formation et collez-le dans la cellule) en deux. Savez-vous comment faire pour faire les deux sur une seule feuille ? Merci!
Ce commentaire a été minimisé par le modérateur sur le site
ce code s'exécute sur la même feuille, mais comment puis-je rechercher la couleur des cellules d'une feuille à une autre dans Excel
Merci d'avance :)
Ce commentaire a été minimisé par le modérateur sur le site
Utilisez cette légère modification du code affiché.


Public xDic comme nouveau dictionnaire
Public strWB As String
Public strWS en tant que chaîne

Fonction CLookup(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next

strWB = LookupRng.Parent.Parent.Name '*** Rappelez-vous le classeur d'où proviennent les données et la couleur
strWS = LookupRng.Parent.Name '*** Rappelez-vous la feuille de calcul d'où proviennent les données et la couleur

Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)

Si xFindCell n'est rien alors
CRecherche = ""
xDic.Add Application.Caller.Address, ""
autre
CLookup = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

Si fin
Fonction de fin

Sub Worksheet_Change (ByVal Target As Range)
Dim I As Long
Dim xKeys aussi longtemps
Dim xDicStr As String
Dim rngLoc As Range
On Error Resume Next
Application.ScreenUpdating = Faux
xKeys = UBound(xDic.Keys)
Si xKeys >= 0 Alors
Pour I = 0 Vers UBound(xDic.Keys)
xDicStr = xDic.Items(I)
Si xDicStr <> "" Alors
Range(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
autre
Range(xDic.Keys(I)).Interior.Color = xlNone
Si fin
Suivant
Définir xDic = Rien
Si fin
Application.ScreenUpdating = True
End Sub
Ce commentaire a été minimisé par le modérateur sur le site
J'ai Windows pour Mac, lorsque j'arrive à l'étape 4 - il n'y a pas d'option pour Microsoft Scripting Runtime, y a-t-il autre chose que je devrais sélectionner ?
Ce commentaire a été minimisé par le modérateur sur le site
Lorsque j'ouvre la fenêtre Afficher le code, il y a une fenêtre mais elle n'est pas vide. Puis-je coller le code sous le texte déjà présent ou comment ouvrir une nouvelle "page vierge" s'il vous plaît ?
Ce commentaire a été minimisé par le modérateur sur le site
Je retourne une valeur, mais je n'obtiens pas la couleur. utilisé le code feuille à feuille, suivi d'un T. Des idées sur la raison pour laquelle je n'obtiens pas la couleur?
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de le modifier pour l'utiliser comme Hlookup ?
Ce commentaire a été minimisé par le modérateur sur le site
bon après-midi bob à ces codes vous pouvez les changer en plus de la couleur appelez moi le même format de couleur et la police qui contient la cellule

Merci
Ce commentaire a été minimisé par le modérateur sur le site
cela fonctionne bien dans office 2010, mais pas la version 2013. Existe-t-il une mise à jour de la macro ?
Ce commentaire a été minimisé par le modérateur sur le site
Salut, puis-je appliquer vlookup sur des cellules de couleur sans données
Ce commentaire a été minimisé par le modérateur sur le site
j'obtiens la couleur de cellule requise mais j'ai également besoin de la valeur de recherche car elle renvoie un entier au lieu d'une chaîne
Ce commentaire a été minimisé par le modérateur sur le site
Je l'ai utilisé dans Excel 2016 et seules les données sont transférées de la source à la cible...….la couleur n'est pas transférée. Réflexions sur le problème potentiel : est-ce une incompatibilité avec Excel 2016 ? Merci. MT
Ce commentaire a été minimisé par le modérateur sur le site
C'était GÉNIAL ! suivi les étapes et cela fonctionne à merveille! Merci!
Ce commentaire a été minimisé par le modérateur sur le site
J'ai de nombreux enregistrements, le traitement prend trop de temps et le code continue de s'exécuter même après l'achèvement. S'il vous plaît aider
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, j'ai une feuille avec 10,948 XNUMX lignes, cela prend du temps pour extraire les informations avec des couleurs, toujours en attente. Est-ce normal ou il y a quelque chose qui ne va pas ?
Ce commentaire a été minimisé par le modérateur sur le site
Comment je fais
Ce commentaire a été minimisé par le modérateur sur le site
J'utilise les heures et les dates des rapports Excel pour créer des feuilles de temps pour nos employés. Si la date spécifiée, par exemple, 2020/08/11 correspond à la date du tableau d'onglets suivant (qui contient de nombreuses cellules avec la même date mais des heures différentes), je veux qu'il tire uniquement la cellule remplie en orange qui sera indiquée comme 2020/08/11 7:45. Est-ce possible?
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour, Ce code fonctionne-t-il pour Office 2016 et les versions ultérieures ?
Ce commentaire a été minimisé par le modérateur sur le site
non sa couleur ne revient pas.
Ce commentaire a été minimisé par le modérateur sur le site
Ce code fonctionne bien, sauf dans les cellules dans lesquelles la formule est entrée, amenez 0 lorsque la cellule qu'il recherche est vide, ma question est de savoir comment puis-je le faire ignorer les cellules vides et empêcher la cellule dans laquelle se trouve la formule d'entrer a 0 , y a-t-il des endroits dans le code pour entrer une fonction =IFERROR peut-être ?
Ce commentaire a été minimisé par le modérateur sur le site
Salut Kyle,

J'ai testé ce code et il n'affiche pas 0 lorsque la cellule qu'il recherche est vide.
Vous pourriez peut-être inclure la formule dans la fonction SI, comme indiqué ci-dessous, pour éviter de renvoyer un résultat de 0.
=SI(B2="","",RechercherConserverCouleur(E2,$A$1:$C$8,3))
Il n'y a pas encore de commentaires postés ici
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés

Nous suivre

Copyright © 2009 - www.extendoffice.com. | Tous les droits sont réservés. Alimenté par ExtendOffice. | | Plan du site
Microsoft et le logo Office sont des marques commerciales ou des marques déposées de Microsoft Corporation aux États-Unis et / ou dans d'autres pays.
Protégé par Sectigo SSL