Ganze Zeile einfärben (bedingte Formatierung)

Bedingte Formatierungen bieten die Möglichkeit, Zellen basierend auf einem Wert zu formatieren (beispielsweise farblich hervorheben). Im Normalfall wird dies auf eine einzelne Zelle angewandt, um darzustellen, dass ein bestimmter Parameter unter- oder überschritten wurde. Ein konkretes Beispiel zeigt der Beitrag "Einen eingefärbten Kalender erstellen". Symbolsätze bieten die Möglichkeit, Trends und Entwicklungen (Pfeile) oder Ampeln und Indikatoren für den - oder gar anstelle des - entsprechenden Wert anzuzeigen. Dies kann in Übersichten und Berichten spannend sein, um auf einen Blick relevante Gegebenheiten erkennen zu können. Dies sind die Symbolsätze, die man unter dem Menüpunkt "Start" → "Bedingte Formatierung" findet:

bedingteFormatierungSymbols

In diesem Blog zeige ich Dir einen Weg, wie Du nicht nur eine einzelne Zelle hervorheben kannst, sondern sämtliche dazugehörigen Informationen in derselben Zeile werden ebenfalls formatiert. Somit werden erfasste Einträge besser ersichtlich und Du kannst Deine Daten beispielsweise nach Farbe filtern (siehe unten). Für unser Beispiel haben wir einen übersichtlichen Datensatz mit Kundeninformationen:

bedingteFormatierungRohdaten

Wir beabsichtigen nun, sämtliche Kunden farblich hervorzuheben, die über eine Kundenkarte verfügen (Spalte G). Um ein erstes Resultat zu erzielen, klicke in die Zelle "G2" und gehe via "Start" → "Bedingte Formatierung" auf "Regeln zum Hervorheben von Zellen" und dort auf "Textinhalt...":

bedingteFormatierungTextinhalt

Damit wird der bestehende Inhalt ("Ja") standardmässig rot hervorgehoben. Mit "OK" lässt sich die Auswahl bestätigen. Man kann jedoch die Formatierung beliebig anpassen, indem man in der Auswahl auf der rechten Seite "benutzerdefiniertem Format..." auswählt. Die Schritte da sind gleich, wie wenn man eine normale Zelle formatiert. Wenn Du die Auswahl bestätigt hast, gehe erneut auf "bedingte Formatierung" im Menüband und wähle dieses Mal zu unterst "Regeln verwalten...". Du siehst diese Option auch im Screenshot oben, wo wir "Regeln zum Hervorheben von Zellen" gefolgt von "Textinhalt..." angeklickt haben. Nun siehst Du die eine Regel, die Du erstellt hast. Gehe oberhalb des einzelnen Eintrags auf "Regel bearbeiten" und wähle anstelle von "Nur Zellen formatieren, die enthalten" die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus. Nun kannst Du weiter unten eine Formel/Kondition eingeben, um bedingte Formatierung auszulösen. Verwende nachfolgende Formel:

=$G2="Ja"

(Das Dollarzeichen ist notwendig, um die Formatierung auf andere Spalten zu übertragen, aber dass die Spalte G und der Wert darin nach wie vor die Bedingung vorgibt.)

bedingteFormatierungFormatierungsregel

Bestätige die Formel und folglich die neue Formatierungsregel mit "OK". In der Übersicht passe nun noch den Bereich der Regel an -  dies ist die Spalte mit der Überschrift "Wird angewendet auf". Wie erweitern da den Bereich auf den gesamten Datenbereich (A2 bis G5). Dies führt dazu, dass alle Werte darin eingefärbt werden, sofern der jeweilige Wert in Spalte G "Ja" entspricht. Weitere Spalten rechts davon (z.B. H und fortfolgende) sind davon nicht betroffen. Damit der Bereich erweitert wird, gib folgende Formel an (die Dollarzeichen sind auch hier zwingend notwendig!):

=$A$2:$G$5

bedingteFormatierungRegelManager

Du solltest die Änderungen bereits aktiv sehen, wenn Du rechts unten auf "Übernehmen" klickst. Du kannst aber auch direkt auf "OK" drücken, um den Regel-Manager zu schliessen. Nun hast Du zwei Einträge komplett eingefärbt, weil der jeweilige Kunde über eine Kundenkarte verfügt:

bedingteFormatierungResultat

Resultate filtern (Farbfilter)

Einleitend habe ich erwähnt, dass das Resultat nun aufgrund der bedingten Formatierung deutlich besser ersichtlich ist. Das Ergebnis ist auch ansprechender, als wenn nur die Zelle in der Spalte G eingefärbt wäre. In jeder beliebigen Spalte kann nun nach der Farbe gefiltert werden, um nur die eingefärbten Zeilen anzuzeigen:

bedingteFormatierungFarbfilter

44 Gedanken zu “Ganze Zeile einfärben (bedingte Formatierung)

  1. Hoi Roman

    Danke für Deine immer sehr einfach erklärten Möglichkeiten. Ich würde gerne die bedingte Formatierung, ausgeweitet auf die gesamte Datensatzzeile, anwenden aber aufgrund einer Teiltext-Suche.

    Beispiel: statt ="June" ="*une". Beim ersten Schritt funktionierts, die Zelle wird markiert, wenn ich jedoch die Bedingung umwandle in Formel zur Ermittlung der zu formatierenden Zellen verwenden, ist wieder fertig.

    Hast Du einen Rat?

    Liebe Grüsse
    Monique

    • Hallo Monique

      Danke für Deinen Kommentar und die Frage.

      Wenn Du *une schreibst, gehe ich davon aus, dass es sich bei "une" um die drei letzten Zeichen von rechts handelt. Du könntest deshalb mit der Formel RECHTS() eine passende Lösung finden - auch innerhalb von bedingten Formatierungen.

      Ich habe die Tabelle aus dem Beitrag oben verwendet und die Spalte "F" in "Geburtstagsmonat" geändert. Wenn da nun "June" oder auch "Iune" (absichtlich falsch geschrieben) steht, wird bei mir die ganze Zeile farblich hervorgehoben. Die Regel der bedingten Formatierung wurde angepasst und sie lautet nun:
      ="une"=RECHTS($F2;3)
      (Diese Formel war zuvor =$G2="Ja" - wie im Beitrag oben dargestellt.)

      Hat das so bei Dir geklappt und das gewünschte Resultat hervorgebracht?

      Beste Grüsse
      Roman

  2. Hallo Roman,
    ich bin absoluter Anfänger was Excel angeht und habe anhand Deiner Formel versucht einen Bezug herzustellen. Ich erstelle gerade eine Ladeliste und möchte zur besseren Übersicht das die Textzeile der Artikelbeschreibung die Farbe ändert sobald ich in die Spalte "Stück" einen Wert eintrage. Ich habe es versucht mit: Wenn größer als 0, dann hellroter Text. Funktioniert super. Wenn ich aber jetzt den Bezug zu den restlichen Spalten herstellen möchte klappt das nicht. Ich habe es mit =$C14=">0" versucht und anschließend den Bereich erweitert auf =$A$14:$J$14. Ich vermute der Fehler liegt bei >0. Hast Du einen Tipp für mich?
    Viele Grüße Ken

    • Hallo Ken

      Danke für Deinen Kommentar und die Frage. Du hast es fast geschafft. Versuch es mal mit dieser Formel:
      =$C14>0

      Ich habe das soeben mit der Beispieldatei von oben ausprobiert; Kundennummer grösser als 30002. Dafür habe ich =$A2>30002 gesetzt und die entsprechenden Zeilen wurde eingefärbt.

      Beste Grüsse & viel Erfolg
      Roman

  3. Hallo Ken,
    Deine Ausführung ist ganz toll beschrieben und wenn ich ein" Ja" in meiner Zelle bräuchte würde alles klappen.
    Ich habe aber eine Umsatzliste und möchte einen Wert der bezahlt wurde in eine Zelle übertragen. Wenn dieser Wert in der Zelle steht, soll sich die ganze Zeile grün einfärben.
    Das würde doch Sinn machen in der bedingten Formatierung ">als" . Er färbt mir aber immer nur die eine Zelle ein.
    Kannst Du mir hier bitte nochmal einen Tip geben?
    Vielen Dank und
    ganz liebe Grüße
    Nicola

    • Hallo Nicola

      Danke für Deine Frage. Die Formel der bedingten Formatierung müsste wie folgt lauten:
      =$A2>1000

      Du musst also keine Anführungszeichen verwenden, um die Kondition "grösser als" abzubilden. Einfach so wie oben reinkopieren. Beachte: Meine Zahl steht aktuell in der Spalte A, deshalb habe ich die Spalte A auch mit Dollarzeichen zusätzlich fixiert (absolut gesetzt). Wo steht Dein Wert, aufgrund dessen die gesamte Zeile eingefärbt werden soll?

      Liebe Grüsse
      Roman

  4. Hallo Ken

    Deine Ausführung funktioniert super - bei mir leider nur für eine Zeile.

    Ausgangslage: wenn ich in der Spalte M einen Eintrag vornehme, egal in welcher Zeile, möchte ich eine grüne Einfärbung der ganzen Zeile programmieren. Wie kann ich die Formel eingeben, damit dies für das ganze Arbeitsblatt umgesetzt wird? Danke für deine Hilfe.

    • Hallo Yvonne
      Klicke bitte einmal eine Zelle an (z.B. in der Spalte M), wo deine Formel und die bedingte Formatierung funktioniert. Gehe danach in die bedingten Formatierung (Menüband Start -> Bedingte Formatierung) und dort auf "Regeln verwalten".

      Im Beitrag oben ist ein Screenshot angezeigt (Manager für Regeln zur bedingten Formatierung) und Du siehst da diese angelegte Regel:
      Formel: =$G2="Ja"

      Kannst Du das einmal mit Deiner implementierten Lösung vergleichen? Womöglich hast Du da sowohl die Spalte (Dollarzeichen vor dem Spalten-Buchstaben) wie auch die Zeile (Dollarzeichen vor der Zeilen-Zahl) fixiert? Achte darauf, dass nur die Spalte (in meinem Beispiel $G) fixiert ist.

      Eine Alternative ist, im gleichen Fenster zu prüfen: Rechts von der Formel, die ich gerade oben aufgeführt habe, prüfe bitte die Angaben in "Wird angewendet auf". Hast Du da den ganzen Bereich ausgewählt, sodass Deine Regel auch da gültig ist? (z.B. =$A$2:$G$5 - wie es im Screenshot im Beitrag gezeigt wird)

      Beste Grüsse
      Roman

      • Hallo Roman,

        ich hab das gleiche Problem wie Yvonne aktuell. Bei mir erscheint dank deiner Formelhilfe eine Zeile in grün aber ich bekomme es nicht auf die anderen Zeilen hin.

        Als Formel hab ich: =$B3="Closed" und bei "wird angewendet auf" hab ich: =$A$12:$AG$41

        • Hallo Miri

          Deine Formel sieht grundsätzlich gut aus und dass Du es auf eine Zeile angewandt bekommst, ist schon einmal gut.

          Kannst Du bitte prüfen:
          Hast Du allenfalls mehrere Regeln in den bedingten Formatierungen, sodass eine die andere übersteuert? Wenn Du auf bedingte Formatierungen -> "Regeln verwalten..." gehst, dann kannst Du oben die Regeln für die aktuelle Auswahl oder für das ganze Arbeitsblatt anzeigen lassen.

          Kommen da je nach Selektion andere Regeln? Kannst Du das mehrmals prüfen auch wenn Du nur einzelne Zellen selektierst und unter "Aktuelle Auswahl" nachschaust? Stimmt hier für jede Zeile die Formel?

          Grundsätzlich benötigst Du im gesamten Arbeitsblatt nur eine einzige Regel mit den Formeln oben.

          Beste Grüsse
          Roman

  5. Hallo Roman,

    ich versuche mich auch gerade an deiner bedingten Formatierung, leider bekomme ich das nicht so hin wie ich es gerne haben möchte.

    Mein Problem ist folgendes, ich ´möchte das wenn in einer Spalte die Zahl >=1 eingetragen wird, die folgende Zeile eingefärbt wird.

    Ich habe es mit der folgenden Formel probiert: =$G$5:$G$5000>=1 den Zellenbereich habe ich auch ausgewählt =$A$5:$X$5400. Habe ich dort einen Denkfehler drin?

    Lieben Gruß

    • Hallo Erik

      Beginne bei der Zeile 5, klicke dafür beispielsweise in die Zelle A5. Gehe auf "Bedingte Formatierungen" -> "Neue Regel...". Wähle dort wie oben beschrieben "Formel zur Ermittlung der zu formatierenden Zellen verwenden" und gib als Formel folgendes ein:
      =$G5>=1
      (Setze noch ein Format, wie Du die Zeile einfärben möchtest)

      Gehe dann erneut auf "Bedingte Formatierungen" und dieses Mal auf "Regeln verwalten...". Setze danach den Bereich "Wird angewendet auf" auf:
      =$A$5:$X$5400

      Bei einer Regel bezieht man sich auf eine einzelne Zelle. Durch die Dollar-Zeichen (absolut setzen) stellen wir sicher, dass die Regel immer auf die Spalte G angewandt wird. Durch den Bereich ("wird angewendet auf") wird das Format auf weitere Zeilen angewandt, da aber die Zeilen-Nummer 5 in der Formel nicht fixiert ist (kein Dollarzeichen), wird jeweils innerhalb der gleichen Zeile in Spalte G nach dem Wert >=1 nachgeschaut.

      Ich hoffe, die Erklärung ist verständlich und Du konntest den Fall lösen!

      Liebe Grüsse
      Roman

  6. Hallo Roman,
    vielen Dank für die klare Anleitung.
    An sich klappt es ganz gut, nur dass wenn ich den Inhalt in der Spalte der Bedingung ändere, die Farbe erst nach speichern oder (irgend)eine Eingabe in eine Zelle sich verändert.
    Weisst du zufällig woran das liegen könnte?

    Anhand deines Bsp. weiter oben passiert folgendes:
    - Spalt G = Kundenkarte -> ist die Bedingung =$G2="Ja" erfüllt, färben sich die Zellen grün
    - Wenn ich in der Spalte G Ja durch Nein überschreibe, bleiben die Zellen grün bis ich die Datei gespeichert habe oder in irgendeiner anderen Zelle etwas eingebe/überschreibe.

    Schöne Grüße, Myriam

    • Hallo Myriam

      Ich habe Deinen Fall nachgestellt und eine Vermutung von mir geprüft: Grundsätzlich kann man die Kondition ändern (also Ja durch Nein ersetzen) und die bedingte Formatierung reagiert unmittelbar darauf. Bitte prüfe einmal unter dem Reiter "Formeln" im Menüband, ob Du bei den Berechnungsoptionen nicht mehr "Automatisch" sondern "Manuell" eingestellt hast.

      In meiner Beispieldatei habe ich die Berechnung auf "Manuell" geändert - dann wird die bedingte Formatierung nicht angezeigt, bevor ich nicht auf "Neu berechnen" klicke.

      Beste Grüsse
      Roman

      • Genau das war es. Du bist mein Held des Tages!! 😉
        Vielen lieben Dank für die schnelle Antwort und die Unterstützung.

        Schöne Grüße,
        Myriam

  7. Hallo Roman,
    mein Problem ist ein bisschen komplexer. Ich möchte die Zelle A1 blau färben, wenn der Wert der Zelle C1 wie folgt aussieht C1>B1/2 und C1< B1. Beispiel: Wenn B1=10 ist, dann soll A1 blau sein, wenn C1=7

    Hoffe ich konnte mich verständlich ausdrücken

    Schöne Grüße, Birgit

    • Hallo Birgit

      Bitte entschuldige meine etwas späte Antwort. Du kannst dies wie folgt erreichen:

      Füge einmal in Zelle B1 die Zahl 10 ein sowie in Zelle C1 die 7 - wie Du es beschrieben hast. Klicke danach in die Zelle A1 und gehe auf "Bedingte Formatierung" -> "Neue Regel...". Wähle daraufhin wie im Beitrag oben ebenfalls die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden".

      Danach gibst Du die folgende Formel in das untere Feld ein:
      =UND($C$1>$B$1/2;$C$1< $B$1)

      Setze dann noch Deine Formatierung (z.B. blau einfärben), klicke auf OK und im nächsten Fenster (Manager für Regeln zur bedingten Formatierung) noch rechts unten auf "Übernehmen" - und Deine Zelle A1 ist entsprechend formatiert!

      Falls Du die Formatierung dann gerne auf weitere Zeilen anwenden möchtest, passe die Formel an zu:
      =UND($C1>$B1/2;$C1< $B1)

      Es sind dadurch nur die Spalten mit den Dollarzeichen fixiert (absolut gesetzt). Als letztes musst Du noch in der Übersicht aller Regeln den Bereich "Wird angewendet auf" zu beispielsweise =$A$1:$A$5 ändern. Wenn Du danach weitere Zahlen einfügst (beispielsweise 12 und 7) werden sie in den entsprechenden Zeilen auch eingefärbt.

      Du hast die Konditionen in Deinem Kommentar schön formuliert, ich konnte sie exakt so übernehmen. Der Schlüssel war die Formel "=UND()", die sowohl die erste (C1 > B1/2) wie auch die zweite Kondition (C1 < B1) als erfüllt verlangt. Die Formel "=ODER()" würde Dir bereits die Zelle einfärben, wenn mindestens eine der Konditionen erfüllt ist. Du kannst übrigens auch mehr als nur zwei Konditionen einpflegen - einfach mit Semikolon (";") trennen. Beste Grüsse Roman

  8. Hallo Roman,
    excellente Erklärung.
    Was kann ich eigentlich tun, wenn ich eine solche Liste habe, aber mit drei verschiedenen Möglichkeiten in der Spalte "G": ja, nein, unbekannt.
    Bei "ja": Grünfärbung der Zeile (das alleine hast du ja sehr gut erklärt und funktioniert bestens). Dann soll aber:
    bei "nein": Rotfärbung der entsprechenden Zeilen und
    bei "unbekannt": Gelbfärbung der entsprechenden Zeilen.
    Wie kann ich das machen? Ich kann doch nicht zwei bedingte Formatierungen in einer Spalte anwenden - oder doch?
    Schöne Grüße
    Stefan

    • Hallo Stefan

      Du kannst sehr wohl mehrere bedingte Formatierungen anwenden. Nachdem Du eine Regel hinterlegt hast, siehst Du sie ja via "Bedingte Formatierung" -> "Regeln verwalten".
      Füge dann einmal eine zweite Regel ein - gehe exakt gleich vor, wie bei Deiner ersten Regel (Deine zweite Kondition - beispielsweise "Nein").

      Wenn Du jetzt erneut in "Regeln verwalten" gehst, müsstest Du beide Regeln sehen; achte je nach Zellen-Selektion darauf, dass Du oben in dem Pop-Up Fenster die Auswahl entsprechend auf "Dieses Arbeitsblatt" setzt. Unter Umständen greifen mehrere Regeln auf eine Kondition (ist in Deinem Beispiel nicht der Fall). Du kannst oberhalb der Regeln mit den Pfeilen die Reihenfolge ändern - die oberste Regel übertrifft alle untenstehenden, falls mehrere gleichzeitig erfüllt wären.

      Liebe Grüsse
      Roman

      • Hallo Roman,

        auch ich bin ein absoluter Anfänger, was Excel-Formeln angeht. Ich bin über die Google-suche auf deinen Beitrag gestoßen und hoffe, dass du mir evtl. helfen kannst.
        Ich habe eine Tabelle in der u.a. ein Beladedatum (C2) und ein Entladedatum (O2) einzugeben ist. Um nun überfällige Belege schneller entdecken zu können (ca. 800 Zeilen), möchte ich gerne eine rote Färbung der leeren Zellen ( "C" oder "O"), falls in der jeweils anderen Zelle bereits ein Datum eingetragen wurde, welches älter als 7 Tage ist. Allerdings nur, wenn nicht bereits in beiden Zellen ein Wert steht oder beide Zellen leer sind.
        Ich hoffe, dies war nicht zu kompliziert erklärt.
        Hier mal ein konkretes Beispiel:
        In C2 steht ein (Belade)Datum, dass schon älter als 7 Tage ist, Zelle O2 (Entlade)Datum ist leer. Jetzt sollte O2 rot eingefärbt werden.
        Sobald der Wert in O2 eingetragen wird (also beide Zellen ausgefüllt sind), soll die Einfärbung wieder aufgehoben sein.
        Das Gleiche in umgekehrter Version:
        Wenn das Datum in O2 älter als 7 Tage ist und in C2 noch kein Eintrag erfolgt ist, soll C2 rot eingefärbt werden, bis der Wert nachgetragen wurde.
        Sollten C2 UND O2 leer sein, sollen die beiden Zellen gelb hinterlegt werden.

        Bisher habe ich mit Hilfe der bedingten Formatierung erreicht, dass mit alle Einträge mit einem Datum älter als 7 Tage rot eingefärbt werden und leere Zellen übersprungen werden. Aber die Einfärbung in Abhängigkeit mit der Überprüfung der anderen Zelle will mir leider nicht gelingen.
        =UND($C2"";$C2<HEUTE()-7)

        Freundliche Grüße

        Melanie

        • Hallo Melanie

          Ein absoluter Anfänger bist Du wohl nicht, wenn Du Dich bereits mit diversen Bedingungen herumschlägst! 🙂

          Ich konnte Deinen Fall mit drei unterschiedlichen Regeln (bedingte Formatierungen) lösen. Du findest unter diesem Link eine Beispieldatei zum Download:
          https://excelblog.ch/wp-content/uploads/BedingteFormatierung_Belade-und-Entladedatum.xlsx

          Dort kannst Du einfach auf "Bedingte Formatierung" -> "Regeln verwalten..." und im Auswahlfenster nicht "Aktuelle Auswahl" sondern "Dieses Arbeitsblatt" auswählen. So siehst Du die hinterlegten Regeln (und auch die Reihenfolge, dass die gelbe Hervorhebung höhergestellt ist - sie kann mit den Pfeilen oberhalb auch umsortiert werden).

          Die Regeln erstelle ich jeweils via Menüpunkt "Start" -> "Bedingte Formatierung" -> "Neue Regel...". Für die Spalte C (Beladedatum) habe ich folgende Regel hinterlegt:
          =UND(UND($C2="";$O2<>"");HEUTE()-7>$O2)
          Die Regel wende ich auf den Bereich =$C$2:$C$1000 an.

          Für das Entladedatum (Spalte O) setze ich diese Regel:
          =UND(UND($C2<>"";$O2="");HEUTE()-7>$C2)
          Die Regel wende ich auf den Bereich =$O$2:$O$1000 an.

          Um leere Fälle hervorzuheben, habe ich diese Regel eingesetzt:
          =UND($C2="";$O2="")
          Sie wird angewandt auf den Bereich =$C$2:$C$1000;$O$2:$O$1000

          Ich hoffe, das klappt so bei Dir mit der Implementierung! Viel Erfolg!

          Beste Grüsse
          Roman

          • Hallo Roman,
            bitte entschuldige das späte Feedback.
            Ich danke dir wirklich sehr für deine Hilfe - es funktioniert alles Bestens und erleichtert mir die Arbeit enorm!
            Wie gut, dass es Experten gibt, die ihr Wissen teilen!

            Nette Grüße
            Melanie

  9. Hallo Roman!

    Vielen herzlichen Dank für diese sehr hilfreiche Anleitung!

    An sich kenne ich mich mit der "Bedingten Formatierung" einigermaßen aus und verwende sie auch oft, weil sie super praktisch ist.

    Leider komme ich aber nicht drauf, warum die Bedingte Formatierung in einer Tabelle, die aus einem ERP-System ins Excel heruntergeladen wird, überhaupt nicht funktioniert.

    Excel-Download aus ERP ist bereits mit Teilergebnis-Zeilen versehen, die keine Formel enthalten, sondern nur Werte und Texte. Auf dieses Download wird eine Tabellen-Formatvorlage übertragen, die im Wesentlichen nur Schrift und Rahmen für die Gesamttabelle und die Überschriftenzeile enthält, sowie einige Spalten gruppiert und ausblendet. Nachher entferne ich noch im Tabellenbereich alle Leerzeilen mit F5 "Gehe zu" und möchte anschließend nur noch die Ergebniszeilen farblich hervorheben.

    Dein Beispiel läßt sich dafür 1:1 in meiner Tabelle umsetzen. In der Spalte A:A beginnt die Ergebniszeile mit dem Text "Ergebnis". Der Bereich der ersten Ergebniszeile ist A2:AG2.

    Sobald ich jedoch im Regelmanager die Formel: =$A2="Ergebnis", Formate und den Bereich für die ganze Ergebniszeile $A1$:$AG1$ definiert habe und auf OK gehe, schließt sich natürlich der Regelmanager, aber es tut sich nichts. Ein nochmaliger Blick auf den Regelmanager offenbart dann, dass gar keine Regel erstellt wurde! Es ist wie verhext und ich bin ziemlich ratlos. Auch andere Tricks wie z.B. mit STRG+H/Erweiterte Optionen/Formate anhand einer Zeilenvorlage suchen/ und auf andere Formate ändern funktionieren in dieser Tabelle nicht, woanders schon.

    Die Suche im Internet nach einer Lösung hat mich zu deinem Blog geführt.

    Ich bin für jeden Tipp dankbar!

    Freundliche Grüße
    Snježana

    • Hallo Snježana

      Ich denke, Du bist sehr nahe an der Lösung dran. Zwei Dinge fallen mir auf:
      Du schreibst "[...] die ganze Ergebniszeile $A1$:$AG1$ definiert [...]" - hast Du das von Deinem Excel so reinkopiert oder bei Deinem Kommentar hier selbst eingegeben? Die Dollarzeichen müssten jeweils zwingend vor die Zeilen-Zahl, d.h. $A$1:$AG$1
      Wenn Du das wie von Dir eingibst, wird der Bezug womöglich nicht angenommen und zurückgesetzt. Ich denke jedoch nicht, dass es daran liegt. Damit die Regel jedoch auch auf weiteren Zeilen greift, solltest Du z.B. $AG$1000 hinterlegen - sonst ist die Regel nur für die erste Zeile gültig (und dort siehst Du wohl keine Farbformatierung aufgrund der Spalten-Überschriften Deiner Tabelle - es scheint dann, wie Du sagst, "wie verhext" zu sein).

      Ein weiterer Punkt, der mit auffällt, ist die Regel in Kombination mit dem angewandten Bereich. Wenn Du =$A2="Ergebnis" setzt, vergewissere Dich bitte einmal, ob die Bezüge / Zeilen nicht verschoben sind, da Du nur die Spalte absolut gesetzt hast (was auch korrekt ist). Wenn Du danach auf "Regel verwalten..." gehst und oben nicht nur die "Aktuelle Auswahl" sondern "Dieses Arbeitsblatt" gewählt hast, scheint dann noch alles zu passen? Wenn Zeilen nicht korrekt fixiert sind oder die Regeln zu Beginn nicht auf der entsprechenden Zeile angewandt werden, kann es zu Verschiebungen kommen. Insbesondere, da Du gemäss Deinem Kommentar =$A2="Ergebnis" mit $A$1:$AG$1 verbindest, versuche es einmal mit:

      =$A2="Ergebnis" und $A$2:$AG$1000 (um die Formatierung auf den Zeilen 2 bis 1000 anzuwenden); oder mit:
      =$A1="Ergebnis" und $A$1:$AG$1000
      So gibst Du auf jeden Fall vor, dass in der gleichen Zeile nach "Ergebnis" gesucht wird.

      Beste Grüsse
      Roman

  10. Hallo Roman,

    super Anleitung selbst für einen Excel Klaus wie mich 🙂
    Wie mache ich es jetzt aber, dass ich diese Formel auf mehrere Blätter in einem excel sheet anwende?

    Da fehlt es mir leider wieder.

    VG Maria

    • Hallo Maria

      Vielen Dank für Deinen Kommentar!

      Wenn Du mehrere ähnliche Tabellenblätter hast, die Du einfärben möchtest, aber die Bedingung immer innerhalb des jeweiligen Blatts geprüft werden soll, dann musst Du einfach den obigen Weg auf jedem Blatt anwenden. Das heisst, so wie Du das für Dein erstes Tabellenblatt hingekriegt hast, müsstest Du auf das zweite Tabellenblatt und die Regel entsprechend dort einpflegen.

      Möchtest Du hingegen auf mehreren Tabellenblätter die Zeilen einfärben, wenn auf einer konstanten Tabelle eine Bedingung zutrifft, dann müsstest Du einfach Deine Formel anpassen. Im Beitrag oben ist eine Formel =$G2="Ja" aufgeführt - diese müsstest Du anpassen zu =Tabellenblattname!$G2="Ja". Wie im ersten Szenario beschrieben, müsstest Du die Schritte (neue Regel erstellen) pro Tabellenblatt wiederholen, um die bedingte Formatierung dort auch anzuzeigen.

      Liebe Grüsse
      Roman

  11. Hallo Roman,

    geht das auch mit Datumsangaben? Ich kann Zellen mit hinterlegter Datumsformel formatieren, wenn ich Sie in Relation zu =Heute() stelle.

    Formel Spalte H < Heute
    =DATUM(JAHR(H197);MONAT(H197);TAG(H197)-180)

    Wenn ich das auf die Zeile ausweiten will, scheitere ich, weil es mir das ganze Blatt einfärbt

    Danke Dawn

    • Hi Dawn

      Du bist meiner Meinung nach auf dem richtigen Weg. Versuch es einmal, indem Du für H197 die Spalte fixierst (absolut setzt) - setze dort einmal ein Dollarzeichen vor das "H" ($H197).

      Bitte stelle zudem sicher:
      - Stimmt die Zeilenangabe 197 denn tatsächlich, d.h. befindest Du Dich auch in der Zeile 197? Nicht, dass etwas verschoben ist.
      - Was ist der Anwendungsbereich Deiner bedingten Formatierung? Gehe dazu auf "Bedingte Formatierung" -> "Regeln verwalten..." und wähle oberhalb nicht "Aktuelle Auswahl" sondern "Dieses Arbeitsblatt" aus. Welche Angabe hast Du nun bei "Wird angewendet auf"? Entspricht das dem Bereich, den Du auch möchtest?

      Etwas anderes könnte sein, dass auch leere Zeilen markiert werden, da die Kondition "H... < HEUTE()" ja eigentlich zutrifft. Versuch es vielleicht mit der Formel "=UND(H...< HEUTE();H...<>"")" - es wird also auch vorausgesetzt, dass überhaupt ein Wert in der Zelle steht!
      (Allenfalls eben auch hier $H... verwenden, wie oben erwähnt)

      Liebe Grüsse
      Roman

  12. Servus Roman

    ich habe ein Dokument in dem ich mehrere Arbeitsblatter mit einer erstellten Datenbank verlinkt habe . Nun möchte ich von Seite "A" eine Berufliche Funktion auswählen - werde über den Link direkt zur Datenbank gebracht und ein entsprechende Name wird markiert. Ist es möglich, mit der Auswahl durch den Link diese Zelle bzw eine ganze Zeile mit bedingter Formatierung dann farblich hervorzuheben? Wie muss die Formel lauten? Bei Auswahl? gibt es bereits eine vorgegebene?
    Danke für deine tollen Erklärungen dir mir schon geholfen haben - aber zu diesem Thema hab ich leider nichts gefunden.

    • Hallo Günther

      Das hört sich für mich nach einem Makro an, das Dir die entsprechende Lösung bieten kann. Hast Du damit Erfahrungen?

      Ausgelöst wird Dein Makro durch eine Änderung auf Seite "A" (eine berufliche Funktion wird ausgewählt). In einem Beitrag "Live-Suche in der Excel Suchmaschine" habe ich ein solches Makro erläutert. Dies wäre der Beitrag dazu:
      https://excelblog.ch/live-suche-in-der-excel-suchmaschine/

      Man steuert das Auslösen des Makros durch Angabe der Zelle (target.Row = 1 And target.Column = 2 für "B1"). Daraufhin müsstest Du den Wechsel auf die Datenbank per Makro angeben und die Zelle entsprechend selektieren (respektive die bedingte Formatierung auf dieser Zelle via Makro eintragen lassen).

      Beachte: Da das "Auslöse-Makro" in dem Tabellenblatt "A" hinterlegt ist, kannst Du nicht per se einen Wechsel auf ein anderes Tabellenblatt durchführen (Sheets("Datenbank").Select würde nicht funktionieren). Setze deshalb globale Variablen, um z.B. die "berufliche Funktion" weiterreichen zu können. Rufe mittels "Call MeinMakro" ein anderes Makro auf, das in z.B. in Modul1 hinterlegt ist - so kannst Du den Wechsel auf das Sheet "Datenbank" und alle dort notwendigen Schritte (bedingte Formatierung setzen) vornehmen.

      Das ist allerdings sicherlich nicht ganz trivial, wenn Du noch nie mit Makros gearbeitet hast - verstehe ich absolut.

      Liebe Grüsse
      Roman

  13. Danke für die ausführliche Instruktion. Makros sind mir grundsäzlich ein Begriff, habe aber nur wenig Erfahrung. Werde mich aber anhand deiner Anleitung durchtüfteln und gebe dann Bescheid über das Ergebnis.
    Gruss aus Salzburg

    • Hallo Günther

      Danke für Deine Rückmeldung. Es gibt noch eine andere Möglichkeit, die Du gerne ausprobieren könntest.

      Ausgehend von der im Beitrag oben aufgezeigten Formel:
      "=$G2="Ja"

      Der erste Teil davon muss zwingend gleichbleiben und bezieht sich ja auf das Tabellenblatt, wo Du entsprechend die Zeile einfärben möchtest (dort steht in Spalte G würde Deine berufliche Funktion stehen). Nun passen wir jedoch den Teil mit '=Ja"' an. In einem zweiten Tabellenblatt (Auswahlliste) habe ich in Zelle "A2" die Drop-Down-Liste hinterlegt, wo man die berufliche Funktion wählen kann. Die Formel für die bedingte Formatierung müsste demnach wie folgt angepasst werden:
      =$G2=Auswahlliste!$A$2

      Wenn der Wert auf der Auswahlliste (Zelle A2) sich verändert, wird automatisch auf dem anderen Tabellenblatt die bedingte Formatierung auf die neue Kondition (z.B. berufliche Funktion) angepasst.

      Vielleicht hilft Dir dieser (simplere) Weg und führt zum gewünschten Erfolg?

      Liebe Grüsse nach Salzburg
      Roman

  14. Hallo Roman, danke für die super Anleitung.
    Ich habe jedoch eine Frage. Für den Fall, den Du oben skizzierst, würde sich doch nur die gesamte Tabelle grün färbe, weil in G2 ein "Ja" steht. Man möchte doch aber, dass für jede Zelle in Spalte G die Prüfung vorgenommen wird, ob ein "ja" hinterlegt ist und die entsprechende Zeile soll sich einfärben. Wie mache ich das? Ich habe eine Spalte mit Datumsangaben. Sobald ein Datum eingeben ist, soll sich die gesamte Zeile grün färben. Bei dir bspw. =ISTLEER($G$2)=FALSCH. Das soll aber für die gesamte Tabelle passieren und ich möchte es nicht für jede Spalte einzeln pflegen. Wie geht das? Danke und viele Grüße

    • Hi Marly

      Bitte beachte: Es wird sich nicht die gesamte Tabelle einfärben, nur weil in G2 ein "Ja" steht, denn wir haben die Prüfung explizit mit den Dollarzeichen auf $G2 angesetzt (nicht $G$2). Es ist zwar die Spalte fixiert worden (also werden auch links und rechts davon Werte eingefärbt, nicht jedoch die Zeile. Das bedeutet, dass jeweils der Wert in Spalte G innerhalb der jeweiligen Zeile geprüft wird. Durch die Angabe des Bereichs in "Wird angewendet auf" steuern wir, wie viele Zellen wir tatsächlich einfärben möchten, respektive wo die Prüfung stattfindet.

      Du bist mit Deiner Formel (=ISTLEER($G$2)=FALSCH) gut unterwegs und hast den richtigen Ansatz. Setze dies einmal auf $G2 - eben ohne die Zeile Nr. 2 absolut zu setzen (kein Dollarzeichen davor). Du könntest alternativ auch die Formel "=ISTZAHL($G2)" verwenden, so wird ebenfalls ein Datumswert eingefärbt (denn hinter jedem Datum steht auch eine Zahl).

      Beste Grüsse
      Roman

  15. Hallo Roman,
    ich habe einen Urlaubsplan in einer Excelmappe erstellt.
    Alle Mitarbeiter in den Zeilen und die Tage über die Spalten. Über alles liegt ein Listenformat, damit man Abteilungen explizit anschauen kann.
    Jetzt zu meiner Frage:
    Es sollen mehrere Personen gleichzeitig eintragen können.
    Andere sollen nur Lesezugriff bekommen um filtern zu können.
    Über Onedrive wäre das ja grundsätzlich möglich, allerdings wird hier die schöne Filtermöglichkeit (anklicken der gewünschten Filter) nicht übernommen (nur über Text möglich). Siehst du eine andere, bessere Lösungsmöglichkeit?

    Herzlichen Dank für deine Hilfe
    lg, Birgit

  16. Hallo, Roman, ich habe folgendes Problem, das ich mir über Excel einfacher machen möchte:

    In einer Tabelle, in der analytische Messwerte eingetragen werden, kommt es vor, dass für zwei Proben mit der gleichen Bezeichnung Werte eingetragen werden. Die Bezeichnung besteht aus Buchstaben und Zahlen.

    Mein Wunsch wäre, dass Excel bei gleicher Probenbezeichnung den Messwert einer bestimmten Zelle vergleicht, und die jeweilige Zeile, die den niedrigeren Wert aufweist, in grüner Schriftart formatiert.

    Also, vereinfacht ausgedrückt:

    in Spalte A steht Text 1, Text 2, Text 2, Text 3 usw.
    In den beiden Zeilen, wo Text 2 drinsteht (gleiche Bezeichnung), soll die jeweilige Zelle in Spalte B, in der Zahlenwerte stehen, miteinander verglichen werden, welcher Wert der kleinere ist. Und diese Zeile soll grün geschrieben werden.

    Ich hoffe, Du kannst mir da weiter helfen.

    • Hallo Manuela

      Hier habe ich mit Hilfsspalten gearbeitet, um eine Lösung auf Deinen Fall zu erarbeiten. Du findest unter diesem Link eine Beispieltabelle:
      Duplikate - niedrige Werte einfärben.xlsx

      Als erstes habe ich neben den bisherigen Daten eine Spalte "Zähler" eingefügt. Diese ermittelt, ob ein Duplikat vorhanden ist (d.h. ein Text mehrmals vorkommt - mit der Formel ZÄHLENWENN).

      Daraufhin wird in Spalte D ermittelt, was denn im Falle eines Duplikats (Zähler grösser als 1) der Minimalwert ist - mit der Formel MINWENNS. Diese lässt uns Konditionen hinterlegen, um nur das Minimum des aktuellen Textes zu ermitteln.

      Die letzte Hilfsspalte enthält dann eine WENN-Formel, die prüft, ob es sich bei der aktuellen Zeile sowohl um ein Duplikat wie auch um den hier gültigen Minimalwert handelt. In dem Falle wird "Niedrigster Wert!" ausgegeben.

      Die bedingte Formatierung prüft dann wie in diesem Blog-Post beschrieben, ob die Kondition in der aktuellen Zelle (Spalte E = "Niedrigster Wert!") enthalten ist. Falls ja, wird die Zelle grün eingefärbt. Du kannst gerne auch eine grüne Schrift hinterlegen unter "Bedingte Formatierung" -> "Regeln anzeigen".

      Die Regel ist auf die gesamte Spalte B hinterlegt. Du könntest unterhalb also einen Text 3 einfügen mit einem bestimmten Wert. Wenn rechts davon in den Hilfsspalten die Formeln entsprechend vorhanden sind, dann wird Dir der niedrigste Wert für Text 3 unmittelbar eingefärbt.

      Liebe Grüsse
      Roman

      • Hallo, Roman,
        Danke für Deine Hilfe, jedoch bin ich in der Umsetzung auf ein Problem gestoßen. Meine Version von Excel scheint zu alt zu sein.
        Die Formel für das Berechnen des jeweils kleinsten Wertes klappt nicht, da die Formel nicht erkannt wird.

        Ich bekomme in Deiner Beispieltabelle für die Formel in der Spalte D bei mir angezeigt:

        =WENN(C2>1;_xlfn.MINIFS(B:B;A:A;A2))

        Hier liegt das Problem. Mein Excel kennt die Funktion MINWENNS offenbar nicht. Scheinbar haben wir eine ältere Version.
        Gibt es für mich eine Alternative, um diese Berechnung trotzdem auszuführen?
        Das mit der Zählerspalte hat einwandfrei funktioniert, und auch die Formel in der Spalte E ist korrekt. Nur sich das Minimum aus beiden Duplikaten zu suchen, daran bin ich ja bisher kläglich gescheitert.

        Liebe Grüße,
        Manuela

        • Hallo Manuela

          Das ist tatsächlich so: Der Fehler erscheint, weil diese Formel in Deiner Version noch nicht verfügbar ist.

          Es gibt eine andere Möglichkeit, wie Du dasselbe Ergebnis erlangst, indem wir "ältere" Formeln verwenden. In der Zelle D2 wäre ja ansonsten diese Formel:
          =WENN(C2>1;MINWENNS(B:B;A:A;A2))

          Setze dafür einfach die nachfolgende Formel ein:
          =WENN(C2>1;MIN(WENN(A:A=A2;B:B)))

          Das sollte Dir ein Resultat liefern.

          Liebe Grüsse
          Roman

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.