Office: Bedingte Formatierung bereich fixieren

Hallo,

ich habe in meiner netten Tabelle einige Bedingte Formatierungen drin. Leider fügen andere Leute ab und an mal Zeilen ein (kann das nicht verhindern). Damit wird dann der Bereich meiner Formatierung verändert.

z.b. ist eine BeFo für den Bereich =$D$5:$AH$1444, nach dem Einfügen von Zeilen innerhalb dieses Bereichs, habe ich dies BeFo mehrmals vorhanden mit jeweils verschiedenen Bereichen.

Habe versucht mit Namen zu Arbeiten aber die BeFo wandelt den Namen nach dem Speichern in den Bereich um, Name verschwunden.

Frage nun: Kann ich alle BeFo eines Sheets auslesen und quasi als Master speichern. Damit kann ich dann mit einem Knopfdruck quasi meinen Ursprung wieder herstellen.

Habe mit dem Makrorecorder mal probiert, klappt aber nicht so richtig. Im aufgezeichneten Code ist eine Zeile schon rot markiert (eine Selection zeile)

Ist der Ansatz möglich oder gibt es andere Lösungen?

Danke

Gruß

:)

 

Hallo,

Du könntest mit Blattschutz arbeiten und dabei das Einfügen von Zeilen nicht zulassen.

Gruß
Aloys

 

Hi,

hast du schon mal versucht, als Bereich die gesamte Spalte anzugeben, also $D:$AH?

 

Hi,

die ganze Spalte angeben kann ich nicht da ich in der BeFo da sie nicht dafür gilt. Blatt sperren ist auch nicht so einfach da ich neue Zeilen hinzufügen muss.

Es ist eine Liste mit Mitarbeiter Namen, diese wechseln des Öfteren (neue, alte löschen).

Kann ich vielleicht mit einem Makro und userform die Eingabe machen und dann mit dem Makro die Zeilen so einfügen damit die BeFo nicht verändert wird?

Z.B am Ende der Liste wo die BeFo noch gilt aber nicht zerpflückt wird.


Danke
Gruß

 

Hi,

nochmal bezüglich ganze Spalte angeben: es spielt doch keine Rolle, ob die bedingte Formatierung für die Zellen bis Zeile 5 gilt oder nicht - so lange sie nicht erfüllt ist (was sicher anzunehmen ist), wird sie auch nicht aktiv. Das selbe gilt auch für die Zeilen unterhalb der eigentlichen Daten. Auf jeden Fall hat dieses Vorgehen definitiv den Vorteil, dass Zeilen eingefügt und gelöscht werden können, ohne dass die bedingte Formatierung nachträglich neu angepasst werden muss.

 

Hi,

du hast Recht, allerdings geht die BeFo erst ab Zeile 5 los, davor soll sie nicht gelten. Oder kann ich noch eine Bedingung einbauen a la wenn(Row(A5)>=5 dann ....

Gruß

 

Hi,

noch einmal - so lange die Bedingung in den Zeilen vor 5 nicht erfüllt ist (was sicher nicht der Fall sein wird), wird die bedingte Formatierung nicht wirksam, also spielt es keine Rolle, dass der Bereich bereits ab Zeile 1 gilt, wenn die gesamte Spalte als Bereich angegeben ist:


Makro

	A	B	C	D
1
2
3
4	AA	BB	CC	DD
5	3	5	6	7
6	6	7	2	5
7

die bedingte Formatierung Fülleffekte, Symbole, Datenbalken
werden in dieser Tabelle nicht dargestellt

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf	Nr. Bed. *Regeltyp	Operator	Formel1	Formel2	Format Schrift Füllfarbe	Unterstrichen	Schrift- farbe	Muster	Musterfarbe	Typ	Bereich
$A:$D	01.Bed.: Zellwert ist	gleich	=5		255					1	A: D

Bedingte Formatierung Haupttabelle 2					oberere/unterer Bereich
Wird angewendet auf	Nr. Bed.	Format Zelle	Anhalten	Auswahl	Anzeige	Anzahl	Durchschnitt	Typ	Bereich
$A:$D	01.Bed.		Falsch					1	A: D

Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 23.05 einschl. 64 Bit

 

Hi
habe das jetzt probiert. Musste aber mit reihennummern arbeiten da ich die Zellfarbe von anderen Zellen und nicht nur vom eigenen Wert abhängig habe.
Alle BeFo sind auf die komplette Spalte und es klappt so auch.

z.b. =$D:$AH
Füge ich nun eine neue, leere Zeile ein, ändert sich die BeFo und ich habe mehrere, gleiche BeFo mit unterschiedlichen Bereichen.

=$D$1:$AH$9
=$D$11:$AH$11
...
=$D$17:$AH$1048574

Übersehe ich da was oder woran liegt das?

Danke

 

Hi,

dann lade doch mal deine Mappe hoch - ich kann das bei mir nicht nachvollziehen.

 

Hi,
hier mal meine Beispieldatei.
Kurze Erklärung:

Spalte B enthält die Namen. Ein Name hat jeweils 8 Zeilen mit Werten(Urlaub, meal,...) für jeden Tag.

Wenn jetzt z.b. ein MA an einem Tag mehr oder weniger als 8 Stunden (jeweils erste Zeile beim Namen) eingetragen hat, es keinen Overtime oder vacation gibt, soll diese Zelle rot werden (Beispiel G13 oder D13)

Wenn allerdings in Spalte A, 2 Zeilen unter dem Namen ein "x", soll die Bedingte Formatierung ignoriert werden.


Hoffe, du kannst das jetzt nachvollziehen.

Danke
Gruß

 

Hi,

und wo ist in deiner Mappe das Problem? Die bedingte Formatierung ist für die komplette Spalte - da gibt es keine Unterteilung in mehrere Bereich und wenn in A15 ein "x" eingetragen wird, dann verschwindet (wie gewünscht) in G13 die Zellformatierung.

 

Hi,

das Problem taucht aber auf wenn ich zwischendrin eine neue Zeile einfüge.
Dann ist die BeFo wieder in Bereiche aufgeteilt.

Das wird dazu führen das nach ein paar Wochen die BeFo Übersicht komplett vermüllt ist!

Das möchte ich verhindern.

Hast du dazu eine Idee?
Danke

 

Hi,

dann hättest du auch solch ein Beispiel hochladen sollen.
Sind die Daten denn nicht vorgegeben, sodass jeder willkürlich Zeilen einfügen darf...?

Um die bedingte Formatierung "zurückzusetzen" musst du eine "Referenzzelle" bestimmen, die von Veränderungen im Tabellenblatt nicht betroffen wird - z.B. D3 - und passt den Bereich neu an:

Code:

 

Hi,
danke schon mal. Ich habe deinen Teil ein wenig ausgebaut.
Idee ist z.b der BeFo 1 und 2 die Spalten zuzuweisen, und alle anderen BeFo zu löschen

Code:

Leider klappt das löschen nicht. Kriege einen Fehler mit VBA.

Ganz allgemeine Frage, ist die Reihenfolge in der BeFo Übersicht gleich mit dem FormatConditions(index). von VBA für den jeweils markierten Bereich?

Danke

 

Hi,

weshalb muss mein Code zu etwas abgeändert werden, das nicht funktioniert, wenn mein Code doch genau das macht was du erreichen willst?

Die VBA-Index entspricht der Reihenfolge der bedingten Formatierung.