So verwenden Sie die bedingte Formatierung, um eine bestimmte Zelle in derselben Zeile wie die aktive Zelle hervorzuheben

    Bild: iStockphoto.com/choness


    Der Artikel So markieren Sie die aktive Zeile, Spalte oder beides mithilfe von VBA und bedingter Formatierung kombiniert ein wenig VBA mit bedingter Formatierung, um die aktive Zeile und Spalte hervorzuheben. Dies ist ein visuelles Element, mit dem der Benutzer abhängige oder verwandte Werte in einem belegten Blatt anzeigen kann. Wie Sie sich vorstellen können, gibt es viele Möglichkeiten, diese Technik zu variieren. In diesem Artikel zeige ich Ihnen eine Lösung, die eine einzelne Zelle in Bezug auf die ausgewählte Zelle hervorhebt, anstatt die gesamte Zeile auszuwählen.

    Mehr über Office

    • 18 Excel-Tipps, die jeder Benutzer beherrschen sollte
    • Microsoft Office, Datenanalyse und virtueller Assistent sind die meistgesuchten beruflichen Fähigkeiten
    • Einstellungskit: Microsoft Power BI-Entwickler (TechRepublic Premium)
    • Abonnieren Sie unseren wöchentlichen Microsoft-Newsletter

    Ich verwende Excel 2016 auf einem 64-Bit-System von Windows 10, aber diese Technik funktioniert in älteren Versionen. Da das Format vom Code abhängt, funktioniert diese Technik in der Browser-Edition nicht. Zur Vereinfachung können Sie die .xlsm- oder .xls-Demonstrationsdatei herunterladen.

    Der Beispieldatensatz und das Setup

    Abbildung A zeigt den Beispieldatensatz. Es ist absichtlich einfach, aber wenn Sie das Beispiel vervollständigen, werden Sie sehen, wie wertvoll es in einer geschäftigen Tabelle sein kann. In diesem Beispiel wenden wir eine bedingte Formatierungsregel an, die das Datum in Spalte C hervorhebt, wenn der entsprechende Betragswert in Spalte E die aktive Zelle ist. Wenn Sie beispielsweise E5 auswählen, wird in unserer bedingten Formatierung C5 hervorgehoben. Sie können mit einem normalen Bereich oder einem Tabellenobjekt arbeiten - die herunterladbare XLSX-Datei enthält beide. Tabellenobjekte werden vom XLS-Format nicht unterstützt.

    Abbildung A.

    Markieren wir das Datum in Spalte C für den ausgewählten Betragswert in Spalte E.

    Wenn Sie in einer Multifunktionsleistenversion arbeiten, speichern Sie die Datei als makrofähige Datei. Klicken Sie dazu auf die Registerkarte Datei, wählen Sie Speichern unter (benennen Sie die Datei gegebenenfalls), wählen Sie in der Dropdown-Liste Dateityp die Option Excel-Arbeitsmappe für Makros (* .xlsm) aus und klicken Sie auf Speichern. Wenn Sie neue Datensätze hinzufügen möchten, sollten Sie den Datensatzbereich in ein Tabellenobjekt konvertieren, wodurch das Format beim Hinzufügen neuer Datenzeilen erweitert wird. Da das Table-Objekt in Menüversionen von Excel nicht verfügbar ist, ist diese Technik für sie nur dann effizient, wenn Sie mit einem stabilen Datensatz arbeiten.

    SIEHE: Bauen Sie Ihre Excel-Kenntnisse mit diesen 10 Power-Tipps auf

        Fügen Sie die Regel hinzu

        Wenn Sie die bedingte Formatierung anwenden, identifizieren Sie normalerweise zunächst die Zelle oder den Bereich, auf den Sie die bedingte Formatierung anwenden möchten. In diesem Fall sind dies die Datumswerte in Spalte C. Wählen Sie zunächst C5: C9 aus und führen Sie die folgenden Schritte aus ::

        1. Klicken Sie auf die Registerkarte Home.
        2. Klicken Sie in der Gruppe Stile auf Bedingte Formatierung und wählen Sie dann Neue Regel aus der Dropdown-Liste aus.
        3. Wählen Sie im Dialogfeld Neue Formatierungsregel die Option Formel verwenden aus, um zu bestimmen, welche Zellen im oberen Bereich formatiert werden sollen.
        4. Geben Sie in das resultierende Steuerelement Formatwerte, bei denen diese Formel wahr ist, den folgenden Ausdruck ein:
          =CELL("address")=CELL("address", $E5)
          Die gemischte Referenz $ E5 stellt sicher, dass die Bedingung nur dann true zurückgibt, wenn sich die aktive Zelle in Spalte E befindet.
        5. Klicken Sie auf die Schaltfläche Format.
        6. Zu diesem Zeitpunkt müssen Sie ein Format auswählen, das sich von der vorhandenen Formatierung unterscheidet. Es liegt an Ihnen, aber klicken Sie in diesem Beispiel auf die Registerkarte Füllen und wählen Sie eine helle Kontrastfarbe aus, z. B. Orange.
        7. OK klicken. Abbildung B zeigt die Formelregel und das Format.
        8. Klicken Sie auf OK, um zum Blatt zurückzukehren.

        Abbildung B.

        Geben Sie die zu erfüllende Bedingung an und formatieren Sie sie.

        Die absolute Referenz auf Spalte E, $ E, beschränkt den Ausdruck darauf, nur dann true zurückzugeben, wenn sich die aktive Zelle in Spalte E befindet. Die Zeilenreferenz 5 ist relativ; Dies ist die erste Zeile in dem Bereich, den Sie zuvor ausgewählt haben (C5: C9). Folglich beschränken Sie den Ausdruck weiter auf die Rückgabe von true nur, wenn sich die aktive Zelle in den Zeilen 5 bis 9 befindet. Mit anderen Worten, der Ausdruck kann nur true zurückgeben, wenn die aktive Zelle E5, E6, E7, E8 oder E9 ist.

        Wenn Sie einen der Betragswerte in Spalte E auswählen, geschieht nichts - noch nicht. Nehmen wir uns eine Minute Zeit, um zu verstehen, warum. Die Formatregel verwendet die Funktion CELL (), für deren Aktualisierung eine erzwungene Berechnung erforderlich ist. Nach meinem besten Wissen gibt es keine flüchtige Funktion auf Blattebene, die die Adresse der aktiven Zelle zurückgibt. Geben Sie die folgende Funktion in Zelle G5 ein, um dieses Verhalten zu demonstrieren:

        =CELL("address")

        Wie Sie in Abbildung C sehen können, gibt die Funktion die Zellenadresse $ G $ 5 zurück. Das haben Sie wahrscheinlich erwartet. Kopieren Sie diese Funktion nun in die Zellen G6: G9 (siehe Abbildung D) . Das ist wahrscheinlich nicht das, was Sie erwartet haben.

        Abbildung C.

        Die Funktion CELL () gibt die absolute Adresse der aktuellen Zelle zurück.

        Abbildung D.

        Die Funktion wird beim Kopieren nicht aktualisiert.

        Sie müssen die Neuberechnung des Blattes erzwingen, indem Sie F9 drücken. Wählen Sie zur Demonstration Zelle D12 aus und Sie werden sehen, dass die CELL () - Funktionen nicht aktualisiert werden. Abbildung E zeigt das Ergebnis der Auswahl von D12 und Drücken von F9 - die CELL () -Funktionen in G5: G9 geben $ D $ 12 zurück, aber erst nach Drücken von F9 zur Neuberechnung werden die Funktionen aktualisiert.

        Abbildung E.

        Berechnen Sie das Blatt neu, um die Ergebnisse von CELL () zu aktualisieren.

        Die Reparatur

        Damit diese bedingte Formatregel funktioniert, müssen Sie das Blatt bei jeder Änderung der Auswahl neu berechnen lassen. Sie können dies tun, indem Sie das SelectChange-Ereignis des Arbeitsblatts verwenden, um das Arbeitsblatt neu zu berechnen. Es besteht eine geringe Wahrscheinlichkeit, dass sich dies auf die Leistung auswirkt, aber dies ist bei den meisten Blättern kein Problem - auch bei stark ausgelasteten. Wenn es Ihnen jedoch passiert, müssen Sie entscheiden, ob der Leistungseinbruch den visuellen Vorteil wert ist, den das Format bietet.

        Um den Code hinzuzufügen, drücken Sie Alt + F11, um den Visual Basic-Editor (VBE) zu öffnen. Wählen Sie im Projekt-Explorer-Bereich links die entsprechende Arbeitsmappe und das entsprechende Blatt aus. Geben Sie den in Listing A gezeigten Code ein . Kopieren Sie diese Webseite nicht und fügen Sie sie nicht ein. Geben Sie den Code stattdessen manuell ein oder verwenden Sie die herunterladbare Demonstrationsdatei. Speichern Sie nach dem Hinzufügen des Codes Ihre Arbeit und kehren Sie zum Blatt zurück.

        Listing A.

         Private Sub Worksheet_SelectionChange (ByVal-Ziel als Bereich) Target.Calculate End Sub 

        Wählen Sie nach der Rückkehr zum Blatt eine beliebige Zelle in E5: E9 aus und beobachten Sie, wie das bedingte Format aktualisiert wird (siehe Abbildung F) . Die CELL () -Funktionen, die Sie zuvor zu Spalte G hinzugefügt haben, sind nicht erforderlich. Wenn Sie sie jedoch nach dem Hinzufügen der Ereignisprozedur aktualisieren, können Sie die Notwendigkeit des Codes verdeutlichen. Die Funktionen von CELL () werden aktualisiert, wenn Sie die Auswahl nur ändern, weil Sie die Ereignisprozedur in Listing A hinzugefügt haben.

        Abbildung F.


        Das bedingte Format funktioniert nach dem Hinzufügen des Codes in Listing A.

        Bleiben Sie auf dem Laufenden - wir werden in den nächsten Monaten weitere bedingte Formate überprüfen, die von der aktiven Zelle abhängen.

        Microsoft Weekly Newsletter

        Seien Sie der Microsoft-Insider Ihres Unternehmens mithilfe dieser Windows- und Office-Tutorials und der Analysen unserer Experten zu Microsoft-Unternehmensprodukten. Wird montags und mittwochs geliefert

        Heute anmelden

        Senden Sie mir Ihre Frage zu Office

        Ich beantworte die Fragen der Leser, wenn ich kann, aber es gibt keine Garantie. Senden Sie keine Dateien, es sei denn, Sie werden dazu aufgefordert. Erste Hilfeanfragen, die mit angehängten Dateien eingehen, werden ungelesen gelöscht. Sie können Screenshots Ihrer Daten senden, um Ihre Frage zu klären. Seien Sie so genau wie möglich, wenn Sie mich kontaktieren. Beispiel: "Bitte beheben Sie Fehler in meiner Arbeitsmappe und beheben Sie, was falsch ist" wird wahrscheinlich keine Antwort erhalten, aber "Können Sie mir sagen, warum diese Formel nicht die erwarteten Ergebnisse zurückgibt?" könnte. Bitte erwähnen Sie die App und Version, die Sie verwenden. TechRepublic erstattet mir weder meine Zeit noch mein Fachwissen, wenn ich Lesern helfe, noch verlange ich von den Lesern, denen ich helfe, eine Gebühr. Sie können mich unter kontaktieren.

        Lesen Sie auch ...

        • Fragen und Antworten zu Office: Wie die wiederkehrenden Aufgaben von Outlook wirklich funktionieren
        • Das Verständnis der bedingten Formatierungsregeln von Excel kann dazu beitragen, unbeabsichtigte Ergebnisse zu vermeiden
        • Verwendung von Präfix-Tags und VBA zum Generieren von bedingtem Inhalt in Word-Dokumenten
        • Sieben Tipps zum Arbeiten mit Office-Formen
        • 12 Tipps und Tricks für eine effektive Kommunikation per E-Mail

        © Copyright 2021 | mobilegn.com