Bedingte Formatierung, wenn Zellen Formeln enthalten

Vielleicht möchtest Du auf Deinem Arbeitsblatt manchmal sehen, welche Zellen Formeln enthalten. Zwei Möglichkeiten dazu möchte ich Dir anbieten.

1. Über die Suchfunktion in Excel

Deine Liste könnte so aussehen:

In B13 steht die Formel

=B12*2

In B17 steht die Formel

            =B13*3

Und in B19 steht die Formel

            =B15*2

Eine Möglichkeit ist, über das Menü Start / Bearbeiten „Suchen und Auswählen“ aufzurufen und dann „Formeln“ auszuwählen.

Die Zellen mit einer Formel werden jetzt in Deiner Liste markiert.

Der Nachteil dieser Methode ist, dass die Markierung wieder verschwindet, sobald Du im Arbeitsblatt weiterarbeitest.

2. Über eine bedingte Formatierung

Eine weitere Möglichkeit, Formelzellen hervorzuheben bietet die bedingte Formatierung.

Definiere zuvor im Menü Formeln / Definierte Namen / Namen definieren den Namen „Formelzelle“.

Unter „Bezieht sich auf“ schreibst Du die Formel

            =ZELLE.ZUORDNEN(48;INDIREKT(„ZS“;FALSCH))

ZELLE.ZUORDNEN ist eine sogenannte Excel4-Makrofunktion.

Das erste Argument (Typ) ist die Zahl 48. Die Zahl 48 gibt WAHR zurück, wenn die Zelle eine Formel enthält, ansonsten FALSCH.

Für das zweite Argument (Bezug) wird die Funktion INDIREKT() benutzt. Mit ihr wird ein Zellbezug auf die Zelle selbst erstellt. 

ZS ist die absolute Adresse der Zelle auf sich selbst in der Z1S1-Schreibweise. Es ist die einzige Adressangabe bei der eine Zelle auf sich selbst adressiert werden kann. Das Argument FALSCH in der Funktion INDIREKT() gibt an, das es sich ausdrücklich um die Z1S1-Schreibweise handelt. [1]

Markiere den Bereich B12:B22. Rufe nun über das Menü Start / Formatvorlagen / Bedingte Formatierung „Neue Regel“ auf und wähle „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.

Gib in das Formelfeld die Formel

            =Formelzelle

ein. Suche unter Formatieren z.B. Ausfüllen aus und wähle eine grüne Farbe.

Nachdem Du auf OK geklickt hast, werden die Formelzellen grün markiert sein. Die Farbe wird erst verschwinden, wenn die entsprechenden Zellen keine Formeln mehr enthalten.

Du kannst somit im Arbeitsblatt weiterarbeiten und behältst die Formelzellen im Auge.

Quellen:

[1]  Die Excel4-Makrofunktion ZELLE.ZUORDNEN() – Microsoft Community

7 Kommentare zu „Bedingte Formatierung, wenn Zellen Formeln enthalten“

  1. So wie’s da oben steht funktionierts nicht und Excel meckert wegen falscher Formel.

    Flasch: =ZELLE.ZUORDNEN(48;INDIREKT(„ZS);FALSCH))
    Richtig: =ZELLE.ZUORDNEN(48;INDIREKT(„ZS“;FALSCH))

  2. Schon wieder falsch… wie kann man hier was als code oder preformatted oder fixed width und ohne typografische Ersetzungen posten?
    Die Anführungszeichen um das ZS herum müssen beide oben sein.

  3. Hallo tmomas,
    danke für Deinen Hinweis. Hier hat sich ein Tippfehler eingeschlichen. Wo gearbeitet wird, passieren auch Fehler. Kennst Du das nicht?
    Richtig muss es heißen:
      =ZELLE.ZUORDNEN(48;INDIREKT(„ZS“;FALSCH))
    Damit funktioniert es.
    VG
    Gerhard

  4. Leider funktioniert das bei mir nur bis zum Schließen der Datei. Beim erneuten Öffnen sind zwar Name und die Regel zur bedingten Formatierung noch vorhanden, aber leider werden die Zellen mit Formeln nicht wie vorher gekennzeichnet. Woran kann das liegen?

  5. Hallo Bernie, ich habe versucht, Deinen Fehler zu analysieren, finde aber keine Ursache. Bei mir funktioniert alles wie beschrieben.
    Versuche bitte folgendes:
    1. Falls sich eine Formel auf eine andere Datei bezieht, muss diese Datei auch geöffnet sein.
    2. Prüfe, ob vielleicht noch eine andere bedingte Formatierung auf Deinen Daten liegt, die evtl. Vorrang hat.
    3. Prüfe noch einmal, ob Du wirklich alles exakt nach meinem Beitrag eingerichtet hast, es schleichen sich schnell mal Fehler ein.
    Falls alles nichts hilft, kannst Du mir Deine Datei gern zusenden, ich würde sie mir ansehen. Aber nur wenn Du willst. Ich behandle alles vertraulich und werde die Datei dann wieder bei mit löschen.
    E-Mail:
    gerhard-pundt@freenet.de
    Viele Grüße
    Gerhard

  6. Ist bei mir auch so. Geht solange, bis die Datei neu geöffnet wird. Dann ist die Formel für die Formatierung noch da und die Formatierung wird nicht mehr ausgeführt.

  7. Hallo, ich habe meine Tabelle noch einmal geprüft. Es funktioniert alles wie beschrieben, auch nach erneutem Öffnen der Datei.
    Ich hatte übrigens vor einiger Zeit aufgrund eines Hinweises einen Formelfehler bereinigt.
    Bei der Definition des Namens „Formelzelle“ muss es bei „Bezieht sich auf“ richtig heißen:

    =ZELLE.ZUORDNEN(48;INDIREKT(„ZS“;FALSCH))

    Viele Grüße
    Gerhard

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s