Document ID: /fineweb-2-swissfilter-quality_10-filterrobots/filtered/03198.jsonl.gz/2054

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:
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:
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...":
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.)
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
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:
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:
98 Gedanken zu “Ganze Zeile einfärben (bedingte Formatierung)”
Coole Anleitung, vielen Dank Roman. Hat bei mir super geklappt!
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
Hallo ihr lieben,
kann mir evtl. jemand weiterhelfen?
Ich habe im Excel eine Tabelle, in der Spalte A ist fortlaufend das Datum (formatiert mit „Datum lang“ dh zB Freitag, 02. Dezember 2022) angeführt. Wenn nun in der Spalte A ein Sonntag vorkommt, müsste die jeweilige Zeile (B4-F404) zB rot befüllt werden. Die Datumsspalte soll allerdings unberührt bleiben. Für diese Spalte besteht bereits eine Regel, dass der Text rot und durchgestrichen ist. Wie kann ich also eine Regel erstellen, dass die jeweilige Zeile ab der Spalte B bis F formatiert wird, wenn in der Zeile ein Sonntag vorkommt? :-/
Ich hoffe jemand kann mir helfen! Danke und GLG Nina
Hallo Nina
Unter Umständen hilft Dir eine Hilfsspalte, auf welche Du Dich beziehen kannst? Um den Wochentag eines Datums auszulesen, verwende einfach "=TEXT(A1;"TTTT")" - z.B. erhältst Du dann für "20.10.2022" (aus Zelle A1) den Wert "Donnerstag" als Resultat. Beziehe Dich in der bedingten Formatierung auf diese Hilfsspalte und lasse diejenigen Zellen/Zeilen einfärben, welche "Sonntag" beinhalten.
Liebe Grüsse
Roman
Danke Roman.
Meine Suche ist im Sinne enthält. Aus Deiner Antwort konnte ich aber ableiten:
=FINDEN("une";$B2).
Passt und funktioniert.
Perfekt
Hallo Monique
Super gelöst, danke für die Rückmeldung!
Beste Grüsse
Roman
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
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
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
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
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
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
Lieber Roman,
danke für deine Antwort. Funktioniert super.
Schöne Grüße, Birgit
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
Vielen Dank für die Beschreibung
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
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
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
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
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
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
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
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
Hallo Roman,
zuerst ein großes Lob für deinen Blog, er ist sehr hilfreich und gut erklärt. Mach weiter so!
Nun zu meiner Frage:
Wie muss ich die Formatierung machen, wenn ich folgendes Ergebnis haben möchte:
Ich habe eine Tabelle mit 8 Spalten, in der dritten (D2) steht der Name, z.B. von einem Lieferanten. Die Farbe soll sich nach diesem Namen richten, z.B. Meier = Gelb, Müller= Blau. Nun möchte ich das alle Zellen in der Zeile mit einem Wert >0 die Farbe des Lieferanten bekommt und alle Zellen 0 hat soll die gesamte Zeile grün werden.
Kannst du mir hier helfen?
Vielen Dank im Voraus
Grüße Jürgen
Hallo Roman,
vielen Dank für die guten Erklärungen. Ich bin absoluter Leihe und bringe mir excell selbstständig bei. Eine Aufgabe steht und schauen was die Profis dazu schreiben. Vlt. können Sie mir helfen:
Ich habe eine Tabelle angelegt und möchte bei Eintragung eines Datums, dass sich die Zeile einfärbt. Dies soll unabhängig vom aktuelen Datum geschehen. Das bedeutet, trägt man ein Datum ein, dann soll die Zeile farbliche dargestellt werden. Die Tabelle befindet sich zwischen den Spalten B bis H. Habe es schon mit der bedingten Formatierung versucht. Habe dazu die von Ihne o.g. Vorgehensweise genommen. Konnte es aber nur mit dem aktuellen Datum realisieren. Bei älteren funktionierte es nicht.
Für Ihre Bemühungen bedanke ich mich im voraus. MfG Sven
Hallo Sven
Vielen Dank für Deinen Kommentar und die spannende Frage.
Hinter jedem Datum steht jeweils eine Zahl (so ist der 27. September 2021 zum Beispiel die Zahl 44466; denn der Tag 01.01.1900 entspricht der Zahl 1). Man könnte also prüfen, ob die Zelle eine Zahl enthält (Formel =ISTZAHL), das kann aber zu unterwünschten Ergebnissen führen, da auch reguläre Zahlenwerte erkannt würden.
Es gibt in Excel eine exaktere Möglichkeit, um zu prüfen, ob die Zelle als Datum formatiert ist. Füge hierzu einmal ein Datum in eine Zelle ein und setze nebenan die untenstehende Formel:
=ZELLE("format";A1)
Falls in "A1" ein Datum steht, erhältst Du "D1" als Formel-Resultat; im Falle einer Zahl lautet das Ergebnis "S".
Dies können wir nun verwenden, um eine eigene Formel für die bedingte Formatierung zu verwenden. Starte dabei möglichst in Zelle "B1" und setze als Formel (wie im Beitrag oben):
=ZELLE("format";B1)="D1"
Wenn Du das jetzt auf den Bereich "$B:$H" abwendest, wird jeweils die Zelle geprüft und gemäss Deiner Formatierung hervorgehoben.
Liebe Grüsse
Roman
Moin Roman,
habe mir hier jede Frage durchgelesen, in der Hoffnung eine Antwort auf meine zu finden...
Leider Fehlalarm 🙂
Ich habe alles Schritt für Schritt befolgt nur für eine deutliche größere Tabelle (ca. 3500 Zellen). Ich habe in der Spalte, die verglichen werden soll 2 Worte drinnen ("Kein Mitglied"). Nun habe ich das Gefühl, dass das System durcheinander kommt, weil es mir auch zwischendurch Zellen markiert, wo nicht "Kein Mitglied" steht.
Vielleicht siehst du ja eine offensichtliche Hürde... Bei Interesse kann ich es auch nochmal ausführlich beschreiben, aber ich denke kurz und knackig reicht eventuell schon aus.
MfG Tom
Hallo Tom
Mit der Grösse der Tabelle sollte es eigentlich keinen Zusammenhang haben (auch 3'500 Zeilen bringt Excel nicht durcheinander). Den Fall habe ich nachgestellt und es funktioniert bei mir.
Mein Begriff "Kein Mitglied" - wie auch andere Angaben - stehen jeweils in Spalte G. Somit lautet meine Regel/Formel:
=$G2="Kein Mitglied"
Anwenden tue ich die Regel auf diesen Bereich:
=$A$2:$G$3500
Kannst Du das so nochmals ausprobieren?
Beste Grüsse
Roman
Guten Tag,
habe ich es richtig verstanden, dass man wirklich jede Formatierung einzeln einpflegen muss?
Einfaches Beispiel:
Arbeitszeittabelle, jede Zeile Samstag/Sonntag soll sich grau färben und Zeilen wie z.B. Urlaub, krank sollen sich auch automatisch grün/blau färben.
Da sind dann also knapp 140 verschiedene Eingaben (pro Monat) nötig?
Vielen Dank und Viele Grüße
Chris
Hallo Chris
Danke für Deinen Kommentar. Nein, Du musst die Formatierung eigentlich nur einmal hinterlegen und kannst via "Bedingte Formatierungen" --> "Regeln verwalten" Deine eingepflegte Regel einsehen und bei "Wird angewendet auf" den Bereich auf ganze Spalten ("$A:$B") oder einen spezifischen Bereich wie "$A$1:$C$100" festlegen. So greift eine Regel direkt auf all diesen Zellen, wenn der Inhalt "Samstag/Sonntag" ist.
Im Blogbeitrag wird die Regel einmalig hinterlegt und auch via "Wird angewendet auf" auf mehrere Zeilen angewandt.
Liebe Grüsse
Roman
Hallo Roman,
deine Erklärung hat mir sehr weiter geholfen!
Allerdings habe ich noch ein etwas komplizierteres Anliegen.
Ich habe die Spalte mit der Bedingung als Dropdownliste angelegt, weil sich der Status in der jeweiligen Spalte immer mal tagesaktuell ändern kann. Nun soll es so sein, dass sich natürlich dann die gesamte Zeile in die neue Farbe der neuen Bedingung umfärbt. Wie kann ich das erreichen? Viele Grüße Sophie
Hallo Sophie
Welche Ausprägungen hat Deine Dropdownliste? Nehmen wir an, Du hast vier Auswahlmöglichkeiten:
Nord, Ost, Süd, West
Die Auswahlmöglichkeit ist nichts anderes, als das "Ja"/"Nein", das ich im Blog-Beitrag aufgeführt habe. Du könntest jetzt also insgesamt viermal eine Regel hinterlegen, jeweils für den jeden Deiner Dropdown-Fälle. Man kann nämlich mehrere Regeln für den gleichen Bereich definieren, es greift dann die erste Regel, die auch zutrifft (die Reihenfolge könntest Du im Regelmanager einstellen, falls notwendig).
Passt das so für Dich? Viel Erfolg!
Liebe Grüsse
Roman
Hallo Roman,
ich weiß leider nicht, was ich falsch mache, aber das funktioniert bei mir irgendwie nicht. Ich habe die Regeln erstmal so angelegt, dass nur die eine Spalte gefärbt wird, wenn ich einen aus den insgesamt 8 Dropdownpunkten ausgewählt habe. Danach habe ich alles so gemacht wie in deiner Anleitung beschrieben, aber es werden dann auf einmal ganz andere, teils willkürliche Zeilen/Spalten eingefärbt, die nichts mit den Punkten zutun haben. Ganze Zeilen werden gar nicht eingefärbt. Irgendwas scheine ich falsch zu machen.
LG Sophie
Jetzt hat es funktioniert! Excel hatte ein Problem mit den Bezeichnungen selbst.
Lieben Dank!!
Hallo Roman,
vielen Dank für die Erläuterung! Ich dachte bisher, sowas wäre nur mit Makros möglich.
Ich habe mich daran versucht, die Formel umzuschreiben: und zwar soll die gesamte Zeile markiert werden, wenn irgendwo in dieser Zeile ein Schlagwort (z. B. "Baum") auftaucht (zum besseren anschließenden Filtern). Also nicht auf eine bestimmte Vergleichsspalte bezogen.
Das hat auch ganz gut geklappt, bis auf 2 Zeilen, die es nicht markiert hat. Wo könnte der Fehler liegen?
Die Formel ist:
=FINDEN("Baum";$DT2:$RH150)
und der Bereich =$A$2:$RH$150
Wenn ich bei Finden schon A2 eingebe, nimmt er einige Markierungen wieder raus, obwohl in den hinteren Spalten der Suchbegriff auftaucht. Woran könnte das liegen?
LG Luisa
Hallo Luisa
Deinen Fall habe ich nachgestellt und die richtige Lösung zuerst mit der "FINDEN"-Formel auch nicht gefunden. So habe ich von Grund auf eine Variante aufgebaut, wie ich auch sonst nach Werten suche (z.B. in einer "Suchmaschine", die auch hier auf dem Blog gezeigt wird).
Die Regel, die ich für die Zeile 2 eingebaut habe, lautet wie folgt:
=LÄNGE(WENNFEHLER(WVERWEIS("Baum";2:2;1;0);""))>0
Die Regel wird angewandt auf den Bereich:
=$A$2:$RH$150
Mit dieser Formel prüfe ich, ob der Begriff "Baum" in der jeweiligen Zeile vorkommt (z.B. 2:2 - was nach unten springt, bis zu 150, gemäss dem gewählten Bereich). Sollte die WVERWEIS-Formel einen Fehler geben, lasse ich den Wert LEER ausgeben ("") - ansonsten erhalten wir "Baum" zurück, dessen Länge mehr als 0 ist.
Anstelle von "2:2" könntest Du auch "$A2:$RH2" verwenden.
Liebe Grüsse
Roman
Danke für die schnelle Rückmeldung 🙂 das funktioniert aber nur, wenn "Baum" der einzige Begriff in einer Zelle ist, oder? Zumindest war das das Ergebnis meines Tests. Ich bräuchte jedoch etwas, das auch "anschlägt", wenn der Begriff nur einer von vielen innerhalb einer Zelle ist, weshalb ich es erst über FINDEN versucht habe. Gibt es da noch eine Möglichkeit?
Als Beispiel: ich suche nach "Kind", dann sollen mir auch die Zeilen markiert werden, wenn irgendwo in einer Zelle "Kinder und Jugendliche", "Eltern-Kind" etc. steht.
Vielen Dank und liebe Grüße
Luisa
Hallo Luisa
Das stimmt natürlich, es wird auf den expliziten Begriff geachtet. Wenn Du allerdings vorher und nachher ein Sternchen (*) einbaust, wird das Ganze flexibler. Versuche es deshalb mit:
=LÄNGE(WENNFEHLER(WVERWEIS("*baum*";2:2;1;0);""))>0
... oder für den Begriff "Kind" mit:
=LÄNGE(WENNFEHLER(WVERWEIS("*kind*";2:2;1;0);""))>0
Du kannst auch "Baum" oder "Kind" schreiben - der WVERWEIS ignoriert Gross- und Kleinschreibung.
Beste Grüsse
Roman
Hallo
Ich habe ein Problem, welches ich denke hat viel mit den beschriebenen zu tun.
Ich habe in Zelle A1 den Text Mo. Di. Mi. etc. nun möchte ich dass wenn Sa. oder So. steht, die nachstehenden Zellen je Zeile, also im Bsp. A2 und A3 eingefärbt werden. Leider finde ich nicht heraus, wie ich eine bestimmte Anzahl Zellen nach einer bestimmten formatieren kann. Zudem Ab Zelle A4 beginnt es wieder von vorne für den Februar. Daher müssen auch die nachstehenden zwei Zellen in der Formel erwähnt werden, da sonst die Formel nicht kopiert werden kann.
Ich bin gespannt auf eure Antwort
Hi Mike
Deine Lösung, um "Sa." und "So." einfärben zu können, sollte wie folgt ausschauen. Definiere zwei Regeln.
Die Formel der ersten Regel lautet:
"=ODER(A1="Sa.";A1="So.")"
Wende diese an auf den Bereich (respektive für die Zeilen, die direkt unterhalb der Tagesangaben liegen, in meinem Fall Zeilen 2 und 5 für die Spalten A bis N und A bis I):
=$A$2:$N$2;$A$5:$I$5
Die Formel der zweiten Regel lautet identisch:
=ODER(A1="Sa.";A1="So.")
Wende diese dann jedoch auf einen Bereich weiter unten an (Zeilen 3 und 6 in meinem Falle, für die beispielhaften Spalten A bis N und A bis I):
=$A$3:$N$3;$A$6:$N$6
So werden Dir die Zellen unterhalb im Falle von "Sa." oder "So." eingefärbt/formatiert.
Beste Grüsse
Roman
Hallo!
Ich bin dabei ein Kalender-Excel-Sheet zu erstellen, welches für jedes Quartal ein Arbeitsblatt hat. Es soll für jedes Monat Samstage, Sonntage und Feiertage schwarz markiert werden, da an diesen Tagen kein Backup läuft (und somit kein Band für's Backup eingelegt werden muss).
Mir fehlt noch die Markierung für die Feiertage. Diese hätte ich gerne in ein eigenes Arbeitsblatt eingetragen, als einfach Liste.
Aber wie bekomme ich das jetzt in eine Formel?
Hi Simon
Wir können die drei Szenarien in einer "WENN"-Formel mittels "ODER" kombinieren. Setzen wir zuerst die Voraussetzungen:
Angenommen, Dein Datum steht in der Zelle A1 (z.B. 01.01.2022). Mittels =TEXT(A1;"TTTT") findest Du den Wochentag heraus - also ob es sich um einen Samstag oder Sonntag handelt.
Führe in einer separaten Tabelle alle Feiertage auf, z.B. in Spalte A die Daten (01.01.2022) und in der Spalte B nebenan die Bezeichnung des Feiertags (z.B. "Neujahr"). Mit einem SVERWEIS kannst Du Dein ursprüngliches Datum aus Zelle A1 suchen. Die Formel wäre z.B.:
=SVERWEIS(A1;Feiertage!$A:$A;1;FALSCH)
Das Ergebnis hier wäre ein Datum, respektive dessen zugrundeliegende Zahl (jeder Tag hat eine Zahl, der 1. Januar 2022 ist die Zahl 44562).
Um nun zu ermitteln, ob mindestens einer dieser Fälle zutrifft, verwende eine einfache "WENN"-Formel:
=WENN(ODER(ISTZAHL(SVERWEIS(A1;Feiertage!$A:$A;1;FALSCH));TEXT(A1;"TTTT")="Samstag";TEXT(A1;"TTTT")="Sonntag");1;0)
Du prüfst zuerst, ob der SVERWEIS eine Zahl zurückgibt - oder ob es sich um einen Samstag, oder um einen Sonntag handelt. Trifft einer dieser Fälle zu, erhältst Du eine "1", ansonsten eine "0".
Du könntest dies in eine bedingte Formatierung einbauen, setze dazu einfach hinter die oben genannte Formel noch eine "[...]=1", dies wäre dann:
=WENN(ODER(ISTZAHL(SVERWEIS(A1;Feiertage!$A:$A;1;FALSCH));TEXT(A1;"TTTT")="Samstag";TEXT(A1;"TTTT")="Sonntag");1;0)=1
So hast Du die Kondition gesetzt, um die Zelle(n) entsprechend einzufärben/zu formatieren.
Bitte beachte: Du musst je nach Aufbau Deiner Tabelle und Absicht allenfalls noch die Bezüge absolut setzen (Dollarzeichen), z.B. falls Du einen grösseren Bereich unterhalb einfärben möchtest die Zeile (bspw. A$1) oder falls Du daneben einfärben möchtest die Spalte (bspw. $A1) usw.
Beste Grüsse
Roman
Super, nach einer solchen Lösung habe ich letztens sehr lange gesucht.
Hallo, ich finde deine Anleitung hier wirklich super.
Aber ich stehe auch total auf dem Schlauch bei einer Teiltext Suche. Vor meiner Kundennummer stehen noch drei Buchstaben "INC" oder "NIC". Ich möchte mir die Zeilen mit den "INC" markieren lassen. Egal wie ich es mache, (FINDEN oder LINKS) es werden immer irgendwelche Zeilen markiert, nur nicht die, in der die Buchstaben stehen.
Für deine Hilfe wäre ich dir echt dankbar.
Hallo Ariane
Vielen Dank!
Deinen Fall konnte ich rekonstruieren und eine Lösung finden. In meinem Fall (wie in den Screenshots im Initialbeitrag hier) steht die Kundennummer in der Spalte A. Ich selektiere dafür die Zelle "A2" und gehe via "Bedingte Formatierung" --> "Neue Regel..." und füge wie oben illustriert eine "Formel zur Ermittlung der zu formatierenden Zellen" ein.
Um diejenigen Zeilen zu markieren, die mit "INC" beginnen, setze ich diese Formel:
=LINKS($A2;3)="INC"
Danach setze ich den Anwendungsbereich entsprechend so, dass es auf die ganze Tabelle gültig ist. Das gewählte Format (z.B. farbige Markierung) greift dann sofort. Kundennummern, die mit "NIC" beginnen, werden nicht markiert; sie müssen aufgrund der LINKS-Formel effektiv mit "INC" beginnen.
Hat es bei Dir so funktioniert?
Beste Grüsse
Roman
Hallo Roman,
ich bin wirklich sehr erstaunt wie gut du Excel beherrscht! Beneidenswert.
Ich habe eine csv Tabelle in Excel umgewandelt. Ich möchte alle Zeilen, die den Teiltext "Miet" enthalten, ganz einfärben. Wenn ich aber den Tabulator nur auf eine Zelle setze, färbt er nur diese ein. Markiere ich den ganzen Text, färbt er auch die anderen Felder ein. Soweit so gut.
Aber wenn ich die ganze Zeile einfärben will, komme ich nicht weiter. Welche Kondition gebe ich hier ein "Formel zur Ermittlung der zu formatierenden Zellen verwenden"?
Viele Grüsse
Dagmar
Hallo Dagmar
Vielen Dank für Deinen Kommentar.
Beginne beim Konfigurieren einer bedingten Formatierung am besten bei der obersten Zeile (z.B. Zeile 2, wenn Du noch Überschriften hast). Gehe dann wie im Initial-Beitrag aufgeführt via "Bedingte Formatierung" --> "Neue Regel..." und wähle die letzte Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus.
Als Formel kannst Du zwei Varianten verwenden. Wie kürzlich in der Antwort an Ariane angegeben, falls Dein Text jeweils mit "Miet" beginnt (und der Text in Spalte A steht):
=LINKS($A2;4)="Miet"
Diese Formel nimmt die vier Zeichen von links.
Als zweite Option, falls "Miet" irgendwo enthalten sein kann, verwende diese Formel:
=FINDEN("Miet";$A2)
Bitte beachte, dass diese "FINDEN"-Formel Klein- und Grossschreibung berücksichtigt.
Beachte bitte zudem, dass das Dollarzeichen gesetzt ist und die Spalte somit angebunden wird (nur so können danebenliegende Zellen auch eingefärbt werden)!
Setze nach Eingabe der Formel die gewünschte Formatierung und abschliessend noch den Anwendungsbereich Deiner Formel. Dies wäre z.B. =$A$2:$G$6 - abhängig von Deiner Tabelle. Berücksichtige bitte, dass ich bei "A2" beginne; das ist auch da, wo ich die Formel gesetzt habe. Ansonsten gibt es eine Verschiebung der Bezüge.
Konntest Du so das gewünschte Resultat erzielen?
Beste Grüsse
Roman
Auch, wenn es eine Weile her ist, hoffe ich, dass mir vielleicht noch jemand helfen kann. Wenn, dann eine(r) von euch!
Wenn in meiner Zelle "Absage" steht, wird die Zeile rot gefärbt. Das funktioniert mit dem tollen Tutorial von Roman schon super.
Wenn ich jedoch noch eine Begründung dazu schreiben will, also "Absage, weil ..." entfärbt es die Zeile wieder.
Habe es schon mit Sternchen probiert, also (=$L2="*Absage*"), aber das funktioniert leider auch nicht.
Hat da jemand eine Idee? Bin auf sonstigen Websites nicht weit gekommen.
Beste Grüße
Marten
Hallo Marten
Du solltest dies mit der untenstehenden Formel lösen können:
=FINDEN("Absage";$L2)
Das markiert Dir sowohl diejenigen Zeilen, die in Spalte "L" nur das Wort "Absage" haben, allerdings auch jene, wo man hinterher noch eine Begründung anfügt ("Absage, weil ...").
Hat das so funktioniert bei Dir?
Liebe Grüsse
Roman
Hi Roman
Hat jemand eine Idee warum es mir immer die nächste Zeile rot einfärbt anstatt der Zeile in der ich den Wert eintrage?
Hier zum Hintergrund:
Ich möchte ein Mitgliederverzeichnis anlegen und in der letzten Spalte ist vermerkt ob das Mitglied bereits ausgeschieden/ausgetreten ist. Eingetragen wird nur ein "x" somit lautet meine Formel: =$K2="x" und angewendet wird es auf die ganze Tabelle =$A$3:$K$35. Allerdings sobald ich das x setze färbt es die nächste Zeile rot aber nicht die betreffende. Ich finde einfach keinen Fehler.
Danke und viele Grüße
Andrea
Hallo Andrea
Bitte prüfe exakt die Formel
=$K2="x"
sowie Deinen Anwendungsbereich. Gemäss Deiner Erläuterung beziehst Du Dich auf die Zelle K2, der Anwendungsbereich ist jedoch von Zeile 3 bis 35. Womöglich muss entweder der Bezug auf Zelle K3 gehen oder der Anwendungsbereich auf $A$2:$K$35 erweitert werden.
Liebe Grüsse
Roman
Hallo Roman
Ich nehme mir ebenfalls die Freiheit und stelle dir eine Frage bezüglich dieser Funktion, da ich glaube (aber nicht weiss), dass es mit der bedingten Formatierung machbar ist.
Es ist so, ich führe eine Liste, welche ich mit dank deiner Anleitung hier erstellt habe (grosses Danke dafür) in welcher ich den Status meiner Anfragen farblich hervorhebe. Es färbt dann die ganze Zeile ein.
- Angefragt: rot
- Erhalten: gelb
- Kontrolle: blau
- Abgeschlossen: grün
(Die Farben tun nichts zu meiner Frage, nur dass du dir's vorstellen kannst).
Im Moment habe ich die Formatierung so, dass wen ich z.B. ein "X" in das Feld 'Angefragt' schreibe, sich die Zeile verfärbt. Wenn ich nun den Status ändern will und z.B. im Feld 'Erhalten' ein "X" eingebe, färbt sich die Zeile in die neue Farbe. So weit so gut, jedoch bleibt das erste "X" bestehen. Da ich dies mehrmals am Tag machen muss, wäre es gut, wenn sich das erste "X" selber löschen würde. So eine Art WENN Funktion. Wenn ein Feld ein "X" enthält, müssen die anderen leer sein. Gibts dafür eine Lösung welche ich vielleicht auch gleich in die Bestehende Formel einbinden kann?
Die Formel fürs einfärben jetzt ist:
=$H9="x"
(das halt für die Spalten H-K welche den Status beinhalten) und färbt wie erwähnt die ganze Zeile.
Vielen herzlichen Dank für deine Hilfe.
Hallo Roman,
ich brauch das täglich zum Bearbeiten riesiger Listen, bin schon daran verzweifelt, dass die so unübersichtlich sind und Du hast mir gerade den PoPo gerettet.
Dank Dir die richtige Formal in die erstel Zelle oben links geschrieben und dann mit "Format übertragen" in die gesamte Tabelle übertragen.
Hat prima geklappt. Vielen Danke!
Hallo Petra
Das freut mich - viel Erfolg weiterhin und danke für Deine Rückmeldung!
Vielen Dank für die aufschlussreiche Anleitung! Allerdings stolpere ich darüber die Regel für die bedingte Formatierung von einer Zeile in die nächste zu übertragen.
Sagen wir ich habe eine Spalte Z in die ich die Werte "grün" oder "rot" eingeben möchte um dann die ganze Zeile zu färben. Also:
=$Z1="grün" / und dann wird angewendet auf: "=$A$1:$Z$1"
Klappt super!
Wenn ich jetzt aber die Zelle kopieren will um die Regel für die bedingte Formatierung auf mehrere Zeilen anzuwenden, wird für "wird angewendet auf" immer der Wert für die Spalte übertragen, also zum Beispiel "=$Z$23" (statt =$A$23:$Z$23).
Ich hab auch versucht mit STRG ALT V zu kopieren, aber ohne Erfolg.
Mein Gefühl ist, ich übersehe etwas Offensichtliches... aber was??
Ja es war wirklich offensichtlich, wenn mal erst mal darauf gestoßen ist. (hat mich nur eine Stunde gekostet.. ohje)
Natürlich muss ich den Anwendungsbereich einfach für meine gesamte Mappe definieren, also z.B. =$Z1="grün" / wird angewendet auf =$A$1:$Z$999 und dann brauche ich nur eine Formel für alles. Das steht ja auch in deinem Beispiel 🙂
Ich lasse meine (doofe) Frage mal stehen und grüße alle, die meine Hassliebe für Excel teilen 🙂
Hallo zusammen,
ich möchte (über ein Aggregat) nur mit Werten belegte Zeilen mit Rahmen versehen, alle nachstehenden leeren Zeilen nicht.
Ich habe keine anderen sich gegenseitig aushebelnden Regeln in der bedingten Formatierung definiert.
In Zelle $A1 wird geprüft:
=$A1>0
für die Formatierung Rahmenlinien
Bereich:
=$A$1:$J$800
Egal welche Zeile, egal welcher Wert in der erste Zelle in Spalte A, ob größer 0 oder gleich 0, wird mit Rahmenlinien versehen. Alles von Zeile 1 bis 800 ist gerahmt, der untere Teil ist aber komplett leer.
Prüfung der Formel in der Bedingten Formatierung >> festgestellter Fehler:
In jeder Zeile prüft Excel =$A1>0.
Klar woran das liegt. Wie bekomme ich es automatisch hin, dass er fie Zeile für die Prüfung wechselt und jede Zeile für sich prüft?
In Zeile 2 muss ja stehen =$A2>0 , in Zeile 3 muss geprüft werden =$A3>0, usw. Kann doch nicht ewa sein, dass ich jede einzelne Zeile bedingt formatieren muss.
Formatierungspinsel hilft zwar, kommt auch durcheinander, wenn ich unterschiedliche Anzahl von Zeilen beim Markieren und Format übertragen auswähle, und macht dann wieder falsche Zeilenadressen in die Prüfung rein. 800 Zeile will ich in einem Aufwasch erledigen.
Gibts einen Trick?
Hallo Tom
Wenn Du eine solche bedingte Formatierung für einen grösseren Bereich setzen möchtest, beginne immer bei der obersten Zelle (in Deinem Beispiel in Zelle A1). So wird der Bezug korrekt gesetzt für Deinen Bereich (über Zeilen 1 bis 800).
Du kannst das nachträglich auch konfigurieren/anpassen, indem Du die erste Zelle (A1) selektierst und in "Regeln verwalten" gehst. Schaue dort, welche Regel hinterlegt ist und ob der Bezug tatsächlich "=$A1>0" ist.
Liebe Grüsse
Roman
Hallo Roman,
vielen Dank für deine tolle Anleitung, das hat für mich auch für eine Spalte sehr gut geklappt. Ich habe aber das Problem für einen Bereich von 60 Spalten und 500 Zeilen diese bedingte Formattierung hinzubekommen. Das hat leider trotz Lesens deiner gesammelten Antworten nicht geklappt (vielleicht habe ich was übersehen). Wie sähe die Formel für einen ganzen Bereich mehrerer Spalten aus? Kann das der Excel-Editor für 'Bedingte Formattierung' überhaupt?
Hallo Martin
Möchtest Du Deine Zeile einfärben, wenn eine Kondition auf mindestens 1 von 60 Spalten zutrifft? Das heisst: Falls beispielsweise "Ja" in irgendeiner Spalte zwischen A (Spalte 1) bis BH (Spalte 60) auftritt, dann soll die Zeile eingefärbt werden?
Selektiere dazu die Zelle A1 und gehe wie im initialen Beitrag via "Bedingte Formatierungen" -> Neue Regel -> "Formel zur Ermittlung der zu formatierenden Zellen verwenden".
Als Formel gibst Du dann ein:
="DEIN_SUCHBEGRIFF"=WVERWEIS("DEIN_SUCHBEGRIFF";$A1:$BH1;1;0)
(und wähle Deine gewünschte Formatierung, z.B. grüne Einfärbung)
Setze danach den gültigen Bereich der bedingten Formatierung auf "=$A$1:$BH$500".
Du solltest nun in der ganzen Tabelle (Zeilen 1 bis 500) Einfärbungen sehen, falls in irgendeiner der Spalten der gewünschte Suchbegriff steht.
Beachte: Dies ist eine exakte Suche. Der Begriff muss also unbedingt übereinstimmen.
Hat das funktioniert?
Liebe Grüsse & viel Erfolg
Roman
Hallo zusammen
ich habe eine Tabelle, in der stehen Leistungsnummern, Stunden, Monat, Woche, Rechnungsnummer und die Namen der Mitarbeiter
Den ersten Part habe ich schon mal hinbekommen, wenn die Leistung =A ist und die Stunden >40, dann soll es grün eingefärbt werden
Basisformel: =UND($N2="A";$Q2>40;)
Jetzt kommt es aber:
Stehen in der gleichen Woche, beim gleichen Mitarbeiter zusätzlich noch Sonntagzuschläge, Schichtzuschläge am Samstag oder Feiertagzuschläge, dann sollen die Arbeitsstunden andersfarbig sein, z. Bsp. orange inkl. der dazugehörigen Zuschläge
Ich denke, es ist eine Und und Oder-Abfrage, aber wie bekomme ich das hin, ohne jedes Mal den Namen und die Woche mit angeben zu müssen? Mit SVERWEIS geht das doch in der bedingten Formatierung nicht. Außerdem kann es in einer Woche mal vorkommen, dass es nur Arbeitsstunden gibt, in der nächsten Woche hatte er/sie nur Nachtschichtzuschläge, die keine Auswirkung auf die Gesamtstunden haben, in der dritten Woche gibt es mal einen Zuschlag oder 2 Zuschläge oder alle 3 Zuschläge, die Blöcke sind also unterschiedlich lang, die Bedingung UND($N2="A";$Q2>40;) gilt aber für alle Blöcke, egal ob Sonntagzuschlag, Feiertagzuschlag, Samstagzuschlag oder Nachtzuschlag dabei ist (UND-Verbindung mit den Arbeitsstunden zu den Zuschlägen, ODER-Verbindung zwischen den Sonn-, Feiertag- und Samstagzuschlägen)
Bsp: für eine Konstellation
Mustermann1, Woche 2, Leistung A, Stunden 45,60
Mustermann1, Woche 3 Leistung A, Stunden 53,20
Mustermann1, Woche 3 Leistung SOZF, Stunden 7,60
Mustermann1, Woche 3 Leistung NZF, Stunden 30,40
Mustermann2, Woche 2 Leistung A, Stunden 53,20
Mustermann2, Woche 2 Leistung SOZF, Stunden 7,60
Mustermann2, Woche 2 Leistung NZF, Stunden 6,00
Mustermann3, Woche 2 Leistung A, Stunden 45,60
Mustermann3, Woche 2 Leistung FTZ, Stunden 7,60
Ich denke, ich kann das auch für jede Zuschlagart machen, aber die steht ja nicht in der gleichen Zeile wie die Arbeitsstunden, sondern darunter,
Vielen Dank für die Hilfe im Voraus, ich wäre froh, wenn ich das ohne VBA hinkriegen würde, in VBA fehlt mir so einiges mehr.
Mit freundlichen Grüßen
Andrea Seyfarth
Hallo,
vielen Dank erstmal für die Erklärung, hat super funktioniert.
Ich habe noch eine Frage.
Und zwar möchte ich in der Zeile noch folgendes einfügen.
Mein Beispiel:
Wenn in Spalte H2-H300 "nein" eintrage, das einzelne Felder z.B. I3+P3 die Felder schraffiert und gesperrt sind zum ausfüllen.
Könntest du mir hierbei helfen ?
Lg
Sprich wenn H3 "nein" dann I3+P3 gesperrt + schraffiert
Hallo Manu
Du kannst gemäss obigem Beitrag die Zelle schraffieren lassen, wenn in der Spalte H jeweils "nein" eingetragen ist. Was jedoch mit bedingten Formatierungen nicht möglich ist, ist das Sperren von Zellen.
Hast Du die nebenan liegenden Zellen der Spalten I und P schraffieren können (mal nur auf diesen Teil der Fragestellung fokussieren)?
Betreffend dem Sperren von Zellen müsstest Du mit einem Makro arbeiten, das bei einer Eingabe in Spalte H jeweils prüft, was eingegeben wurde. Das ist etwas komplexer, aber das Makro könnte Dir dann im Falle von einer "nein-Eingabe" die entsprechenden Zellen nebenan sperren oder auch schraffieren/einfärben (so entfallen auch bedingte Formatierungen, wenn es der VBA-Code sogleich übernehmen soll).
Die Herangehensweise dazu ist in diesem Beitrag rund um "Live-Suche in der Excel-Suchmaschine" erläutert:
https://excelblog.ch/live-suche-in-der-excel-suchmaschine/
Du würdest etwas mit "target.Column = 8" (für Spalte H) als Auslöser verwenden.
Beste Grüsse
Roman
Hi Roman
Thema: Mit Bedingter Formatierung (BF) nicht nur die Zelle mit der Bedingung, sondern die ganze zugehörige Zeile einfärben.
Was habe ich lange herumprobiert ohne eine allgemeingültigen und nachhaltigen Erfolg zu erzielen. Immer wieder hatte die BF unter irgendeiner anderen Bedingung wieder nicht funktioniert. Erst nachdem ich heute auf Deine Anleitung gestossen bin, wurde mit mein einziger Fehler bewusst: Ich hatte in der Bedingung/Formel die Spalte nicht absolut gestellt ($).
Deine Beschreibung hat mit sehr geholfen. Sehr ansschaulich beschrieben! Da schau ich jetzt öfteres mal rein.
Danke und Gruss ROLF
Hallo Rolf
Danke Dir für den Kommentar. Das freut mich zu hören! Viel Erfolg & Spass weiterhin!
Liebe Grüsse
Roman
Hallo Rolf!
Ich habe eine bedingte Formatierung erstellt, welches die ganze Zeile markiert, aber die Bedingung modifiziert, die die zu markierende Zeile auswählt: Nun funtkioniert die bed.Formatierung nicht mehr? Kannst du mir bitte weiterhelfen.
Formatiere wenn in der Zelle J9 ein "x" steht UND in der Zelle K ein "-" steht UND in der Zelle L ein "-": =$J$9="x" & $K$9="-" & $L$9="-"
Danke,
Rob
Hi Rob
Packe am besten Deine Anforderungen in eine "=UND()" Formel, in etwa so:
=UND($J$9="x";$K$9="-"&$L$9="-")
Sofern alle drei Bedingungen erfüllt sind, sollte die bedingte Formatierung greifen (das Resultat ist WAHR, in allen anderen Fällen FALSCH und somit wird nichts formatiert).
Beste Grüsse
Roman
Sorry very much: Hallo Roman!
Moin Roman,
Ein sehr guter Beitrag von Ihnen.
Ich habe meine Tabelle soweit erstellen können aber bin noch nicht ganz zu frieden. In meiner Tabelle wird immer um weitere Positionen erweitert, wenn ein Material angelegt wird. Diese Zeile soll deswegen in rot erscheinen soweit ein variabler Name in der gewünschten Zelle/Spalte auftaucht.
Hallo Marvin
Vielen Dank!
Du hättest zwei Optionen:
- Quick-and-dirty wäre, den Bereich für bedingte Formatierungen einfach etwas grosszügig zu setzen (z.B. auf Zeilen 1 bis 10'000).
- Alternativ helfen Dir vielleicht die Konzepte zum Verschieben von Bereichen und Namen, um dynamisch zu erkennen, wie viele Einträge vorhanden sind. Das ist etwas komplexer, aber hier sind die Basics dazu aufgeführt: https://excelblog.ch/bereich-eines-namens-automatisch-erweitern/
Liebe Grüsse
Roman
Hallo Roman
Das mit dem Kopieren der Bedingten Formatierung habe ich gelesen, kann es aber nicht auf meinen Fall beziehen.
Ich möchte die Zeile 1 von A bis C grau einfärben, wenn in A1 etwas drinsteht.
So habe ich die bedingte Formatierung verwendet und die Formel =$A1"" wird angewendet auf =$A$1:$C$1 eingetragen. Das funktioniert einwandfrei.
Wenn ich die Formel nun herunterziehe (kopiere) funktioniert die Formel nicht mehr. Ich kann unter, wird angewendet auf, zwar =$A1:$C1 eingeben, dass wird dann aber sofort wieder in =$A$1:$C$1 korrigiert.
Es muss noch möglich sein, wenn eine Zelle ausgefüllt ist (mit was auch immer), dass dann eine ganze Zeile eingefärbt wird (was ja auch klappt - nur leider nicht kopierbar auf das ganze Blatt ist).
Hast du eine Idee dazu?
Gruss
Thomas
Hallo Thomas
Würde es denn so funktionieren?
Selektiere die Zelle A1 und gehe in die bedingten Formatierungen, um die neue Regel zu definieren. Setze dann die folgende Regel:
=$A1<>""
(und definiere, dass Du solche Zellen grau einfärben möchtest)
In der Regel-Verwaltung siehst Du dann Deine neue Regel. Wenn Du oberhalb bei "Formatierungsregeln anzeigen für" in der DropDown von "Aktuelle Auswahl" auf "Dieses Arbeitsblatt" umstellst, kannst Du dann nicht die Regel "Wird angewendet auf" für =$A$1:$C$1 konfigurieren? Respektive für beispielsweise =$A$1:$C$500, um sie auf viele weitere nachfolgende Zeilen ebenfalls anzuwenden?
Liebe Grüsse
Roman
That's it!
Du bist ein Genie und ich danke dir herzlich für deine wertvolle Hilfe.
Liebe Grüsse
Thomas
Vielen lieben Dank, Thomas! Viel Erfolg weiterhin!