May 15, 2023, updated at 07:33:24 (UTC)

2707

Excel - Markierung für Wochenenden autom. erweitern

Hallo,

ich habe eine Excel-Datei mit 12 Blöcken (für jeden Monat einen).
In der jeweils ersten Spalte stehen die Datums-Angaben.

Die Wochenenden werden über eine bedingte Formatierung automatisch farblich markiert... ebenso die Feiertage.
Das geht über folgende Formeln:

Feiertage:

=SVERWEIS(A6;Feiertage!$A$2:$A$16;1;0)

Wochenenden:

=WOCHENTAG(A6;2)>5

Das funktioniert sehr gut.

Nun gibt es in den 12 Monatsblöcken jedoch nicht nur die Datums-Spalte, sondern daneben jeweils weitere 10 Spalten.
Diese sollen ebenfalls mit eingefärbt werden, wenn es sich um ein Wochenende handelt (Sa/So), bzw. einen Feiertag.

Meine bisherigen Recherchen ergaben, dass dies mit einem zusätzlichen $-Zeichen gehen soll... z.B. so:

=WOCHENTAG($A6;2)>5

Aber das funktioniert nicht.
Zum besseren Verständnis hier mal ein Screenshot, wie das Ganze aussieht:

Wer kann weiterhelfen?

Grüße von
Yan

Please also mark the comments that contributed to the solution of the article

Content-Key: 7159591854

Url: https://administrator.de/contentid/7159591854

Printed on: June 18, 2024 at 13:06 o'clock

13 Comments

Latest comment

Hola.

Aber das funktioniert nicht.

Doch das tut es, man muss nur den Bereich für die bedingte Formatierung selbstredend erweitern!
It works as designed ... Kannst du dir hier ansehen: Demo

Gruß

Hallo und danke für den Tipp und die MP4-Datei.

wie ja eingangs beschrieben, hatte ich die Lösung mit dem zusätzlichen $-Zeichen bereits getestet.
Aber das funktioniert nicht.

Es liegt wohl daran, dass ich 12 Blöcke (3 x je 4 nebeneinander --> siehe Screenshot) habe.
Und wenn ich alle zu formatierenden Blockbereiche gleichzeitig markiere und dann eingebe

=WOCHENTAG($A6;2)>5

dann ziehen sich die Markierungen zwar durch die nebeneinander stehenden Blöcke... aber dann werden z.B. im zweiten Block auch andere Wochentage mit in grau eingefärbt, da diese sich dann in der gleichen Zeile befinden, wie ein Samstag/Sonntag im Block links daneben.

Ich habe mal einen Screenshot eingefügt, wo man den Fehler sieht:

Oder geht es nur, wenn ich 12 einzelne bed. Formatierungen eingebe?
Aber das wäre natürlich aufwendig, denn ich muss das Gleiche ja auch noch für die Feiertage machen. Das wären dann 24 einzelne bed. Formatierungen und ich kann mir nicht vorstellen, dass dies die einzige Möglichkeit wäre.

Kannst Du... Ihr... nochmal helfen?

Grüße von
Yan

Zitat von @Yan2021:
Oder geht es nur, wenn ich 12 einzelne bed. Formatierungen eingebe?

Ja, musst du natürlich einzeln machen, sonst bezieht sich die Formel ja auch bei den anderen immer nur auf Spalte A.

Aber das wäre natürlich aufwendig, denn ich muss das Gleiche ja auch noch für die Feiertage machen. Das wären dann 24 einzelne bed. Formatierungen und ich kann mir nicht vorstellen, dass dies die einzige Möglichkeit wäre.

Lässt sich noch auf 10 reduzieren (bei 10 Spalten neben dem Datumswert) wenn man die Spalten mit den gleichen Abständen zur Datumsspalte zusammenfasst und mit STRG markiert.
Markieren musst du sowieso einzeln, kannst du auch mit nem Makro kopieren wenn dir das zu viel Handarbeit ist. Aber das macht man ja nur einmal bei einem automatischen Kalender

, später muss man da dann eh nicht mehr ran.
Man kann natürlich auch mit WENN DANN verschachteln, daber das dauert am Ende dann wohl länger als es schnell manuell zu machen.

Danke Dir...

Das mit den 10 Spalten und gleichen Abständen zur Datumsspalte... habe ich noch nicht verstanden.
Kannst Du das bitte nochmal erklären?

Danke...

Grüße von
Yan

usw.

OK und danke für den Tipp.

Habe jetzt alles umgesetzt und es funktioniert sehr gut

Grüße von
Yan

Wobei auch eine Formel ala

=WOCHENTAG(BEREICH.VERSCHIEBEN(A2;0;-REST(SPALTE(A2)-1;6));2)>5

ginge. Wobei hier die 6 die Anzahl der Spalten bis zur nächsten Datumsspalte wäre (in meinem Beispiel unten). Dann kann man auch alle Bereiche auf einmal wählen und hat nur eine einzige bed. Formatierung

WOW!!!

Jetzt habe ich mir zwar schon die Arbeit gemacht mit den ganzen bed. Formatierungen, aber ich nehme dennoch lieber die einfache Lösung.

Funktioniert ebenfalls perfekt!!

Eine Bitte noch:

Für die Feiertage habe ich ja folgenden bed. Formatierungs-Code eingegeben:

=SVERWEIS(A6;Feiertage!$A$2:$A$16;1;0)

Wie passe ich diesen jetzt an Deinen Code an, den Du für die Wochenenden erstellt hast?

Grüße von
Yan

Mit
BEREICH.VERSCHIEBEN(A2;0;-REST(SPALTE(A2)-1;6)) kommst du immer an die Zelle in der das Datum steht, das musst du nur an der Stelle einsetzen wo in der SVERWEIS Formel das Suchkriterium steht ... feddisch.

=SVERWEIS(BEREICH.VERSCHIEBEN(A2;0;-REST(SPALTE(A2)-1;6));Feiertage!$A$2:$A$16;1;FALSCH)

Oh, das ist perfekt.
Danke für Deine weitere Hilfe...

Ich hatte es mit verschiedenen Varianten versucht, aber ich hatte den Teil "-REST(SPALTE(A2)" immer an der falschen Stelle eingesetzt

Sorry... aber eine kleine Frage hätte ich noch:

Da ja jetzt die Wochenenden und Feiertage schön bis nach rechts durchgefärbt werden, wäre es jetzt perfekt, wenn man in diesen eingefärbten Zellen (also den 10 Spalten rechts neben dem Datum) KEINE Einträge machen könnte.
Aber... wie kann man das umsetzen... also quasi diese Zellen die durch ein WE od FT farbig formatiert wurden, automatisch zu sperren?

Ich vermute mal, dass das vielleicht auch eher über VBA-Code geht, statt mit einer bedingten Formatierung... oder?

Grüße von
Yan

Zitat von @Yan2021:
Sorry... aber eine kleine Frage hätte ich noch:

Das geht jetzt langsam zu weit. Man sollte in Threads immer beim eigentlichen Thema bleiben nicht ausschweifen!

Da ja jetzt die Wochenenden und Feiertage schön bis nach rechts durchgefärbt werden, wäre es jetzt perfekt, wenn man in diesen eingefärbten Zellen (also den 10 Spalten rechts neben dem Datum) KEINE Einträge machen könnte.
Aber... wie kann man das umsetzen... also quasi diese Zellen die durch ein WE od FT farbig formatiert wurden, automatisch zu sperren?

Zelle markieren > STRG+1 drücken -> Schutz -> Gesperrt. Alle anderen Zellen die bearbeitet werden dürfen, markieren und das Häkchen entfernen. Dann Tab -> Überprüfen -> Blatt schützen.

Bitte nun auch als gelöst markieren!

Hallo und danke nochmals...

OK, der Zusammenhang war nicht so ganz gegeben, ist aber m.E. dennoch vorhanden.
Aber wie ich grds. Zellen sperre, weiß ich natürlich schon.

So könnte ich das für die AKTUELLE Datei ja auch machen.
Aber im kommenden Jahr passen dann die gesperrten Zellen ja nicht mehr, da die Wochenenden in anderen Zellen (Zeilen) stehen werden.

Daher müßte man einen Zusammenhang herstellen, zwischen den erkannten Wochenenden und den 10 Zellen daneben. Quasi eine Formel: "Wenn WE od. FT, dann 10 Zeilen rechts daneben sperren".

Ich kann gerne auch einen eigenen Thread dazu eröffnen, aber da aus meiner Sicht die Themen durchaus zusammen gehören, würde ich das gerne noch hier lösen.

Danke und Grüße von
Yan

Hallo nochmal @7010350221,

ich habe nochmal eine Rückfrage zu den Wochenend-/Feiertagenbereichen, die auch über weitere Spalten hinweg farblich markiert werden sollen.

In meiner Orig. Datei funktioniert das sehr gut mit dem folgenden Code von Dir (bedingte Formatierung):

=WOCHENTAG(BEREICH.VERSCHIEBEN(A6;0;-REST(SPALTE(A6)-1;12));2)>5

Die Datumsangaben stehen in Spalte A (erstes Datum in "A6") und die letzte einzufärbende Zelle steht in Spalte "K".

Der nächste Block beginnt dann in Spalte "M" (also 12 Spalten bis zum nächsten Datum), was dann auch die Datums-Spalte ist (erstes Datum in "M6").

Wie gesagt, passt das mit diesem Code perfekt!!

ABER...

In einer anderen Datei habe ich nur 2 Monatsblöcke:

Dort stehen im 1. Block die Datumsangaben in Spalte K (erstes Datum in "K4") und die letzte einzufärbende Zelle steht in Spalte "U".

Im 2. Block (also ebenso 12 Spalten bis zum nächsten Datum) stehen die Datumsangaben in Spalte W (erstes Datum in "W4") und die letzte einzufärbende Zelle steht in Spalte "AG".

PROBLEM:

Ich habe für diese Datei den oben stehenden Code entsprechend angepaßt... und zwar so:

=WOCHENTAG(BEREICH.VERSCHIEBEN(K4;0;-REST(SPALTE(K4)-1;12));2)>5

Dabei wurden jedoch fast alle Zellen grau.
Dann habe ich den Code etwas verändert, indem ich statt der "12" im Code, nun eine "10" eingetragen habe.

Das paßte schon fast... bis auf die Spalte "U", die jetzt komplett grau ist (siehe Screenshot). Ich habe es auch mit "11" versucht, aber auch das passte nicht.

Zu diesen beiden Blöcken habe ich mal einen Screenshot angehangen, wo man das Problem sieht.*

Ich kann diesen Fehler nicht nachvollziehen, da die Blöcke die gleichen Abstände haben, wie auch in meiner Orig. Datei, wo es ja perfekt funktioniert.

Kannst Du @7010350221 oder sonst Jemand von Euch nochmal weiterhelfen?
Und... mir auch die Formel nochmal erklären? Ich möchte es einfach nur verstehen, warum es hier nicht funktioniert.

Grüße von
Yan

German Question Microsoft Office