Format einer Zelle auslesen und Zeile einfärben

Ein vielbesuchter Beitrag hier auf Excelblog.ch illustriert, wie sich eine ganze Zeile mit bedingten Formatierungen einfärben lässt (siehe Ganze Zeile einfärben (bedingte Formatierung)). Darin wird dargestellt, wie aufgrund einer Zellen-Angabe eine gesamte Zeile farbig hervorgehoben werden kann. Dabei wird meist nach einem bestimmten Text gesucht. In diesem Beitrag stelle ich Dir eine weitere Möglichkeit dar, um auf das Format einer Zelle einzugehen und eine entsprechende Einfärbung zu bewirken.

Für das Einlesen des aktuellen Zellenformats verwenden wir die Formel "ZELLE". In einem anderen Post wurde diese Formel bereits verwendet, um den aktuellen Tabellenblatt-Namen auszulesen (Mittels Formel den Tabellenblatt-Name ausgeben). Gehen wir zuerst auf die Formel im Einzelnen ein, anhand eines Beispiels:

Füge in der Zelle "A1" einen beliebigen Wert ein, zum Beispiel ein Datum wie 17.05.2022. Füge dann die untenstehende Formel in eine beliebige Zelle ein (z.B. "A2"), um das Format der Zelle "A1" auszulesen:

=ZELLE("format";A1)

Falls in "A1" tatsächlich ein Datum steht, erhältst Du "D1" als Formel-Resultat. Ändere nun das Format der Zelle "A1", beispielsweise zu Standard. Beachte: Das Formel-Resultat verändert sich nicht umgehend (eine technische Anmerkung: Obschon unter Formeln die Berechnungsoptionen auf "Automatisch" eingestellt sind). Aktualisiere die "ZELLE"-Formel erneut (gehe dafür in den "Bearbeitungsmodus" und bestätige die Formel mit der Enter-Taste erneut. Nun wird Dir für die "Standard-Formatierung" ein "S" ausgegeben.

Weitere Format-Optionen und Formel-Resultate

Schauen wir uns einige weitere Optionen an, teste gerne auch eigene Fälle und aktualisiere jeweils die "ZELLE"-Formel, um das Resultat zu sehen:

Formel ZELLE format

Beachte ein Muster bei Zahlen oder Prozentwerten und deren Dezimalstellen: Der Zähler erhöht sich jeweils, wobei "P" für Prozent (percentage) steht; "F" repräsentatiert "figure" (Engl.: Zahl).

Anwendung zur Einfärbung einer Zelle oder gesamten Zeile

Mit diesen Vorkenntnissen können wir nun eine bedingte Formatierung bei der Zelle selbst hinterlegen, in unserem Beispiel die Zelle "A1". Gehe dazu via "Start" → "Bedingte Formatierung" auf "Neue Regel...". Wähle dort die unterste Option "Formel zur Ermittlung der zu formatierenden Zelle verwenden" und füge die nachfolgende Formel ein:

=ZELLE("format";A1)="D1"

Wir wissen ja, dass wir im Falle eines Datums (17.05.2022) "D1" als Formel-Resultat zurückerhalten. Wir prüfen demnach, ob die Zelle A1 entsprechend formatiert ist und färben die Zelle daraufhin grün ein:

Formatierungsregel ZELLE Format

Um eine gesamte Zeile einzufärben, basierend auf der Angabe in Spalte "A", müsstest Du die Formel leicht anpassen, mit einem Dollarzeichen vor "A1":

=ZELLE("format";$A1)="D1"

Wir fixieren damit nur die Spalte, nicht die Zeile (da wir wahrscheinlich die bedingte Formatierung auf einen grösseren Bereich anwenden möchten; zum Beispiel von A1 bis F100. Setze dadurch in den bedingten Formatierungen unter "Regeln verwalten" den Bereich "=$A$1:$F$100" bei "Wird angewendet auf":

Formatierungsregel angewendet auf

Du kannst unter "Regel bearbeiten" Deine Formel jederzeit anpassen (z.B. auf "P2" für den gesuchten Prozentwert im Format "85.00%") sowie die Formatierung (Farbe, Schriftart, etc.) setzen.

Beachte: Wir haben oben erwähnt, dass die Formel bei einer Änderung der Formatierung erneut eingegeben werden muss. Das ist bei der bedingten Formatierung nicht der Fall. Solltest Du also einen Wert und dessen Formatierung anpassen (z.B. von einem Datum in einen Prozentwert umwandeln), wird umgehend die (bedingte) Formatierung zur Anwendung kommen.

Konfigurationsdatei in Excel (Config)

In einem älteren Beitrag habe ich einst aufgeführt, dass ein Blatt "Parameter" zum professionellen Präsentieren von Excel-Tabellen und -Reports hilfreich ist (siehe diesen Blogeintrag). Wichtige Angaben oder Inhalte von Drop-Down-Listen werden als Einträge auf einem (ausgeblendeten) Tabellenblatt hinterlegt. Eine Konfigurationsdatei (Config) ist dem sehr ähnlich, die hier vorgestellten Ansätze könntest Du durchaus auch in einem "Parameter"-Sheet hinterlegen.

Eine Konfigurationsdatei dient dazu, an einem zentralen Ort bestimmte Einstellungen vorzunehmen, es ist aus Wartungssicht deshalb deutlich komfortabler, als Anpassungen in diversen Formeln, Tabellenblättern oder VBA-Codezeilen vorzunehmen. Insbesondere bei grösseren Dateien oder Skripten ist es je länger je unübersichtlicher, eine Config-Datei ist dort empfohlen. Man bindet also möglichst viele übergreifenden Angaben, die allenfalls ändern könnten (manuell geändert werden müssen) in eine Konfig-Datei ein. Ein weitergehendes Beispiel könnten Dateipfade sein - respektive dynamische Pfade und Ordnerverzeichnisse. Stelle Dir vor, Du teilst Deine Datei mit einigen Arbeitskollegen. Je nach dem, wenn Ihr das File lokal öffnet, befindet sich die Datei jedoch in einem anderen Verzeichnis. Wenn man neben dem aktuellen File auch eine zweite Datei im Stammverzeichnis ansprechen möchte (z.B. eine Input- oder Vormonats-Datei), dann führen starre Pfade zu Fehlern. Im Beitrag "Dateipfad mit einer Formel darstellen" sind die Grundlagen aufgeführt, wie ein dynamischer Pfad hinterlegt werden kann.

Hier führe ich Dir eine Beispieldatei auf, die ein Blatt "Config" enthält:

Config- Konfigurations-Datei.xlsx

Es werden Variablen definiert, die allerdings im Rahmen dieses Blogs nicht weiter verwendet werden. Du könntest diese entsprechend einem Diktionär (Variable des Typs "Dictionary") einlesen - insbesondere im VBA-Kontext. Du kannst aber auch selber simplere Definitionen mit Zellbezügen anstellen. Relevant für den Excel-Teil an und für sich ist der Inhalt in der Spalte B: Dort sind unterschiedliche Varianten der Datei oder des Stammverzeichnisses (Root Path) direkt ausgelesen. Je nach dem, wo die Datei abgespeichert ist, sind andere Werte (und dort relevante Gegebenheiten) eingetragen.

Abhängige (dynamische) Drop-Down-Listen

In Formularen sind abhängige Drop-Down-Listen oftmals unabdingbar. Was bedeutet das? Wählt man im ersten Feld eine Oberkategorie "Obst" oder "Fleischwaren", dann sollen in einer zweiten Auswahlliste nur die entsprechenden Einträge zur Option stehen. Wir haben diese abhängigen Drop-Down-Listen bereits im Beitrag "Dropdown einer Dropdown-Liste erstellen - abhängige Dropdown" illustriert. Das funktioniert dann einwandfrei, wenn sich die Inhalte der Unterkategorien nicht ändern. Ansonsten ist es notwendig, die Bereiche (Namen) von "Obst" oder "Fleischwaren" den neuen Umständen entsprechend anzupassen (Bereich erweitern oder reduzieren). Es suggeriert also eine dynamische Drop-Down-Liste. Wie man einen solchen Namen (mit der Formel BEREICH.VERSCHIEBEN) anlegt, kannst Du in diesem Beitrag nachschlagen: Bereich eines Namens automatisch erweitern

Wir würden also alle Namen für die Drop-Down-Listen entsprechend anlegen, wie im Screenshot unten dargestellt (mit der Formel "BEREICH.VERSCHIEBEN", referenzierend auf das separate Blatt "Parameter"):

Namenslisten-Dynamisch

Wählt man im Anschluss im Formular die erste Option zu "Essen", sollte in der abhängigen Drop-Down-Liste dann die jeweilige Unterkategorie abrufbar sein. Dies ist jedoch nicht der Fall, die Auswahl-Liste lässt sich mit Klick auf den Erweiterungspfeil gar nicht erst aufrufen und anzeigen. Wie können wir also sowohl abhängige wie auch dynamische Drop-Down-Listen erstellen? Das Vorgehen zeige ich Dir nachfolgend Schritt für Schritt auf, lösche jedoch bitte im Namensmanager (Menüpunkt "Formeln" → "Namensmanager") allfällige bereits definierten Namen für Deine Drop-Down-Listen.

Abhängige, dynamische Drop-Down-Listen (Lösungsansatz)

Wir müssen die ursprünglichen Daten auf dem separaten Blatt "Parameter" also anders definieren, um jederzeit unsere Auswahl-Kriterien ergänzen und unmittelbar nachher im Formular abrufen können. Gehe dazu im Menüband auf das Register "Einfügen" und selektiere die Option "Tabelle". Du erstellst nun also eine Tabelle und wählst als erstes den Bereich "A1:A3" aus, um die Oberkategorie abzubilden. Falls Du die Zeile 1 inkludierst, wähle zudem "Tabelle hat Überschriften".

TabelleEinfuegenUndDefinieren

Wiederhole den Vorgang auch für die Unterkategorien "Fleischwaren" und "Obst".

Wenn Du nun in der Spalte A Fleischwaren und Obst markierst, siehst Du links oben im Namensfeld (normalerweise steht da die Zelle "A2" und so weiter) "Tabelle1" stehen. Der Name "Tabelle1" ist aktuell also stellvertretend für diesen Bereich. Klicke in dieses Namensfeld und überschreibe einfach "Tabelle1" mit "Essen" - bestätige mit der Enter-Taste. Du wirst in dem Namensfeld kurz etwas aufblinken sehen, danach steht allerdings wieder "Tabelle1" drin. Mache das nun auch für B2:B3 (=Tabelle2) und setze da den Namen "Fleischwaren" sowie für C2:C4 (=Tabelle3) und definiere da "Obst". Rufe nun erneut Deinen Namensmanager auf (Menüpunkt "Formeln" → "Namensmanager"). Dort siehst Du die Resultate entsprechend - beachte auch die Spalte "Bezieht sich auf", die auf die jeweilige Tabelle referenziert:

NamensmanagerTabellenReferenzen

Navigiere nun auf Dein effektives Formular. Wähle die Hauptkategorie (Essen) und versuche nun, Deine Unterkategorie (Drop-Down-Einstellung: =INDIREKT(B5) -- wobei B5 dem Feld mit Fleischwaren/Obst entspricht) kann nun korrekt aufgerufen werden:

FormularDynamischeDropDownUnterkategorie

Zuletzt kannst Du prüfen, ob Deine Listen auf dem Blatt "Parameter" tatsächlich dynamisch sind. Gib einfach einen neuen Begriff unterhalb ein, beispielsweise "Schweinefleisch" als zusätzliche Fleischware. Die Tabelle erweitert sich automatisch (Du siehst dies auch farblich) - und auf der Drop-Down-Liste im Formular kannst Du die neue Option ebenfalls gleich selektieren.