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

Wie kann ich die Hintergrundfarbe zusammen mit dem Suchwert in Excel anzeigen und zurückgeben?

Angenommen, Sie haben eine Tabelle wie im folgenden Screenshot gezeigt. Jetzt möchten Sie überprüfen, ob sich ein bestimmter Wert in Spalte A befindet, und dann den entsprechenden Wert zusammen mit der Hintergrundfarbe in Spalte C zurückgeben. Wie wird dies erreicht? Die Methode im Artikel kann Ihnen bei der Lösung des Problems helfen.

Vlookup und Rückgabe der Hintergrundfarbe mit Lookup-Wert durch benutzerdefinierte Funktion


Vlookup und Rückgabe der Hintergrundfarbe mit Lookup-Wert durch benutzerdefinierte Funktion


Gehen Sie wie folgt vor, um einen Wert zu suchen und den entsprechenden Wert zusammen mit der Hintergrundfarbe in Excel zurückzugeben.

1. Klicken Sie im Arbeitsblatt mit dem Wert, den Sie anzeigen möchten, mit der rechten Maustaste auf die Registerkarte Blatt und wählen Sie Code anzeigen aus dem Kontextmenü. Siehe Screenshot:

2. In der Öffnung Microsoft Visual Basic für Applikationen Fenster, bitte kopieren Sie den folgenden VBA-Code in das Code-Fenster.

VBA-Code 1: Vlookup und Rückgabe der Hintergrundfarbe mit dem Lookup-Wert

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. Dann klick Insert > Modulund kopieren Sie den folgenden VBA-Code 2 in das Modulfenster.

VBA-Code 2: Vlookup und Rückgabe der Hintergrundfarbe mit dem Lookup-Wert

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. Klicken Sie nach dem Einfügen der beiden Codes auf Tools > Referenzen. Dann überprüfen Sie die Microsoft Script-Laufzeit Box in der Referenzen - VBAProject Dialogbox. Siehe Screenshot:

5. Drücken Sie die Taste Andere + Q Tasten zum Verlassen des Microsoft Visual Basic für Applikationen Fenster und gehen Sie zurück zum Arbeitsblatt.

6. Wählen Sie eine leere Zelle neben dem Suchwert aus und geben Sie die Formel ein =LookupKeepColor(E2,$A$1:$C$8,3) in die Formelleiste und drücken Sie die Eingabetaste.

Hinweis: In der Formel E2 enthält den Wert, den Sie suchen werden, $ A $ 1: $ C $ 8 ist der Tabellenbereich und die Nummer 3 bedeutet, dass sich der entsprechende Wert, den Sie zurückgeben, in der dritten Spalte der Tabelle befindet. Bitte ändern Sie sie nach Bedarf.

7. Wählen Sie weiterhin die erste Ergebniszelle aus und ziehen Sie den Füllgriff nach unten, um alle Ergebnisse zusammen mit ihrer Hintergrundfarbe zu erhalten. Siehe Screenshot.


In Verbindung stehende Artikel:


Die besten Tools für die Office-Produktivität

Kutools für Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%

  • Wiederverwendung: Schnell einfügen komplexe Formeln, Diagramme und alles, was du vorher benutzt hast; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Super Formelriegel (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen ohne Daten zu verlieren; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen / Spalten... doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
  • Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
  • Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
  • Mehr als 300 leistungsstarke Funktionen. Unterstützt Office / Excel 2007-2019 und 365. Unterstützt alle Sprachen. Einfache Bereitstellung in Ihrem Unternehmen oder Ihrer Organisation. Vollständige Funktionen 30 Tage kostenlose Testversion. 60 Tage Geld-zurück-Garantie.
kte tab 201905

Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50% und reduziert täglich Hunderte von Mausklicks für Sie!
officetab unten
Kommentare (32)
Bewertet 5 aus 5 · 1 Bewertungen
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wie ändere ich diesen Code, damit er die Hintergrundfarbe aus einem anderen Blatt extrahiert?
Zum Beispiel möchte ich in Blatt 2 einen SVERWEIS verwenden, der die Daten und die Hintergrundfarbe aus Blatt 1 extrahiert.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe genau diese Frage! Jeder Rat wäre sehr willkommen.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich möchte auch auf Blatt 2 SVERWEISEN und die Daten und die Hintergrundfarbe aus Blatt 1 extrahieren
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Verwenden Sie diese geringfügige Änderung des geposteten Codes.


Öffentliches xDic als neues Wörterbuch
Öffentlicher strWB als String
Öffentliche strWS als Zeichenfolge

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

strWB = LookupRng.Parent.Parent.Name '*** Erinnere dich an die Arbeitsmappe, aus der die Daten und die Farbe stammen
strWS = LookupRng.Parent.Name '*** Merken Sie sich das Arbeitsblatt, aus dem die Daten und die Farbe stammen

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

Wenn xFindCell dann nichts ist
CLookup = ""
xDic.Add Application.Caller.Address, ""
sonst
CLookup = xFindCell.Offset(0, xCol - 1).Wert
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

End If
End Function

Sub Worksheet_Change(ByVal Ziel als Bereich)
Dimme ich so lange
xKeys so lange dimmen
Dim xDicStr als Zeichenfolge
Dim rngLoc als Bereich
On Error Resume Next
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
Wenn xKeys >= 0 dann
Für I = 0 bis UBound(xDic.Keys)
xDicStr = xDic.Items(I)
Wenn xDicStr <> "" Dann
Range(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
sonst
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Nächste
Setze xDic = Nichts
End If
Application.ScreenUpdating = True
End Sub
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Soll damit ein Fehler im Originalcode behoben werden, oder soll damit von einem anderen Blatt aus nachgeschlagen werden?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Diese Änderung des ursprünglichen Codes ermöglicht es Ihnen, die Suche mit Farbe von einem Arbeitsblatt zu einem anderen oder von einer Arbeitsmappe zu einer anderen durchzuführen. Dieser Code muss jedoch in das TARGET-Arbeitsblatt und nicht in das SOURCE-Arbeitsblatt eingefügt werden, wie im ursprünglichen Code beschrieben. Das liegt daran, dass der ursprüngliche Code nur in einem Arbeitsblatt funktionierte, also sowohl die Quelle als auch das Ziel war. Dies ist keine Korrektur des ursprünglichen Codes. Ich habe gerade Code hinzugefügt, damit Sie aus jeder Arbeitsmappe/Arbeitsblatt (Quelle) in Ihr Arbeitsblatt (Ziel) ziehen können. Der ursprüngliche Code funktionierte wie vom Programmierer beabsichtigt.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich habe das Verfahren durchgeführt, aber ich kann die Hintergrundfarbe nicht in das neue Arbeitsblatt bringen. Ich habe Zweifel, ob ich die Befehle strWB und strWS richtig eingegeben habe. Ich habe diese strWB = LookupRng.Reporte_Opcionales eingefügt
strWS = LookupRng.Imprimir Reporte_Opcionales ist der Name meiner Arbeitsmappe
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich glaube, die Zeilen sollten die folgenden sein (GENAU):

strWB = LookupRng.Parent.Parent.Name

strWS = LookupRng.Parent.Name


Ich bin vor ungefähr 4 Monaten darauf gekommen, also erinnere ich mich nicht genau, wie ich darauf gekommen bin, aber Sie sollten diesen Code nicht durch irgendetwas anderes ersetzen.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
was hat der name in strWB wiederholt Parent.Parent ???? stimmt das?
Vielen Dank im Voraus.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Bob, hilf mir bitte, könntest du bitte den Code überprüfen? Ich bin sicher, dass Sie es beheben können, da es die Hintergrundfarbe von einem anderen Blatt überbrückt.

übrigens der code, der für die arbeit in demselben blatt gilt, aber ich muss die daten von einem anderen blatt mitbringen :(.

Vielen Dank im Voraus
Grüße aus Monterrey México.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das funktioniert super, danke!
Bewertet 5 aus 5
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Dieser Code arbeitet auf demselben Blatt. Wie kann ich die Farbe von einem Blatt zum anderen nachschlagen?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Verwenden Sie diese geringfügige Änderung des geposteten Codes.


Öffentliches xDic als neues Wörterbuch
Öffentlicher strWB als String
Öffentliche strWS als Zeichenfolge

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

strWB = LookupRng.Parent.Parent.Name '*** Erinnere dich an die Arbeitsmappe, aus der die Daten und die Farbe stammen
strWS = LookupRng.Parent.Name '*** Merken Sie sich das Arbeitsblatt, aus dem die Daten und die Farbe stammen

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

Wenn xFindCell dann nichts ist
CLookup = ""
xDic.Add Application.Caller.Address, ""
sonst
CLookup = xFindCell.Offset(0, xCol - 1).Wert
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

End If
End Function

Sub Worksheet_Change(ByVal Ziel als Bereich)
Dimme ich so lange
xKeys so lange dimmen
Dim xDicStr als Zeichenfolge
Dim rngLoc als Bereich
On Error Resume Next
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
Wenn xKeys >= 0 dann
Für I = 0 bis UBound(xDic.Keys)
xDicStr = xDic.Items(I)
Wenn xDicStr <> "" Dann
Range(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
sonst
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Nächste
Setze xDic = Nichts
End If
Application.ScreenUpdating = True
End Sub
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Bob! Der Code funktioniert jedoch aus irgendeinem Grund, er kopiert Werte von Blatt 2 nach Blatt 1, kopiert aber die Zellenformatierung und belässt sie in Blatt 2 ... Es ist schwer zu erklären, aber es teilt im Grunde eine Aktion auf (Text kopieren + Formation kopieren und fügen Sie es in die Zelle ein) in zwei. Weißt du, wie man es schafft, beides auf einem Blatt zu machen? Vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Dieser Code wird auf demselben Blatt ausgeführt, aber wie kann ich die Zellenfarbe von einem Blatt zu einem anderen Blatt in Excel nachschlagen
Vielen Dank im Voraus :)
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Verwenden Sie diese geringfügige Änderung des geposteten Codes.


Öffentliches xDic als neues Wörterbuch
Öffentlicher strWB als String
Öffentliche strWS als Zeichenfolge

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

strWB = LookupRng.Parent.Parent.Name '*** Erinnere dich an die Arbeitsmappe, aus der die Daten und Farben stammen
strWS = LookupRng.Parent.Name '*** Merken Sie sich das Arbeitsblatt, aus dem die Daten und die Farbe stammen

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

Wenn xFindCell dann nichts ist
CLookup = ""
xDic.Add Application.Caller.Address, ""
sonst
CLookup = xFindCell.Offset(0, xCol - 1).Wert
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address

End If
End Function

Sub Worksheet_Change(ByVal Ziel als Bereich)
Dimme ich so lange
xKeys so lange dimmen
Dim xDicStr als Zeichenfolge
Dim rngLoc als Bereich
On Error Resume Next
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
Wenn xKeys >= 0 dann
Für I = 0 bis UBound(xDic.Keys)
xDicStr = xDic.Items(I)
Wenn xDicStr <> "" Dann
Range(xDic.Keys(I)).Interior.Color = Application.Workbooks(strWB).Worksheets(strWS).Range(xDic.Items(I)).Interior.Color
sonst
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Nächste
Setze xDic = Nichts
End If
Application.ScreenUpdating = True
End Sub
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe Windows für Mac, wenn ich zu Schritt 4 komme – es gibt keine Option für Microsoft Scripting Runtime, gibt es etwas anderes, das ich auswählen sollte?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wenn ich das Fenster „Code anzeigen“ öffne, wird ein Fenster angezeigt, das jedoch nicht leer ist. Kann ich den Code unter den bereits vorhandenen Text einfügen oder wie öffne ich bitte eine neue "leere Seite"?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich gebe einen Wert zurück, erhalte aber nicht die Farbe. Ich habe den Blatt-zu-Blatt-Code verwendet, gefolgt von einem T. Irgendwelche Ideen, warum ich die Farbe nicht bekomme?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Gibt es eine Möglichkeit, dies zu ändern, um es als Hlookup zu verwenden?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Guten Tag Bob zu diesen Codes können Sie sie zusätzlich zu der Farbe ändern, rufen Sie mich an, das gleiche Farbformat und die gleiche Schriftart, die die Zelle enthält

Vielen Dank
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Dies funktioniert in Office 2010 einwandfrei, aber nicht in der Version 2013. Gibt es ein Update für das Makro?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, kann ich vlookup auf Farbzellen ohne Daten anwenden?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich erhalte die erforderliche Zellenfarbe, aber ich brauche auch den Suchwert, da er eine Ganzzahl anstelle einer Zeichenfolge zurückgibt
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe dies in Excel 2016 verwendet und es werden nur die Daten von Source to Target übertragen....Farbe wird nicht übertragen. Gedanken zu möglichen Problemen: Ist es mit Excel 2016 inkompatibel? Vielen Dank. MT
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Das war FANTASTISCH! habe die Schritte befolgt und es funktioniert wunderbar! Vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe viele Datensätze, die Verarbeitung dauert zu lange, und der Code läuft auch nach Abschluss weiter. Bitte helfen Sie
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich habe ein Blatt mit 10,948 Zeilen, es dauert einige Zeit, die Informationen mit Farben zu ziehen, ich warte immer noch. Ist das normal oder stimmt etwas nicht?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wie es mir geht
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich verwende Zeiten und Daten aus Excel-Berichten, um Arbeitszeittabellen für unsere Mitarbeiter zu erstellen. Wenn das angegebene Datum, zum Beispiel 2020/08/11, mit dem Datum im nächsten Registerkarten-Array übereinstimmt (das viele Zellen mit demselben Datum, aber unterschiedlichen Zeiten enthält), möchte ich, dass es nur die orange gefüllte Zelle zieht, die als angegeben wird 2020 08:11. Ist das möglich?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, funktioniert dieser Code für Office 2016 und spätere Versionen?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Nein, es gibt keine Farbe zurück.
Es sind noch keine Kommentare vorhanden
Hinterlassen Sie Ihre Kommentare
Als Gast posten
×
Bewerte diese Nachricht:
0   Figuren
Vorgeschlagene Standorte