Office: Bedingte Formatierung in Makro

Hallo zusammen,

ich habe folgendes Problem bei meiner Excel-Tabelle:

Ich habe mittels eines Buttons ein Makro implementiert, das bei Betätigung eine neue Zeile generiert (ein neuer Datensatz).
In dem Makro möchte ich auch, dass für jede Zeile eine bedingte Formatierung generiert wird. In meinem Fall soll eine Spalte grün, gelb oder rot angezeigt werden, wenn eine bestimmte Bedingung in der Spalte daneben erfüllt ist.

Kurz: Spalte J = erledigt, dann Spalte K grün ausfüllen
Spalte J = offen, dann Spalte K gelb ausfüllen usw.

Nun zu dem Problem: Bei der ersten neuen Zeilen, die ich mit dem Makro-Button generiere funktioniert es, bei der 2 Zeilen nicht mehr, dann wird die bedingte Formatierung für die vorherige Zeile auch mit angewendet. Siehe Screenshoot:screen 1.jpg

Ich würde gerne das die Regeln dann fortlaufend generiert werden; sprich, dass das rot makierte dann zu K10,K11 usw. wird durch einfügen neuer Zeilen. Ist das überhaupt möglich?

Anbei noch mein VBA-Code:screen 2.jpg

Ich hoffe das war einigermaßen verständlich und würde mich freuen, wenn mir jemand helfen kann.

Danke schon mal im Voraus


Christian

:)

 

Hi Christian,

Vermeide das Hochladen von Bildern anstelle von Beispieldateien oder Tabellenausschnitten, denn es gibt keine Möglichkeit, ein Bild in eine funktionierende Exceltabelle umzuwandeln, außer dass man die Daten von Hand abschreibt. Dadurch steht den Helfern wesentlich weniger Zeit zur Verfügung, sich mit deinem eigentlichen Problem zu beschäftigen.
Erstelle stattdessen eine Demodatei, aus der deine Aufgabenstellung erkennbar ist und lade diese Datei mit einem

 

Hallo Beverly,

du hast natürlich völlig recht, Screenshots sind echt unvorteilhaft in diesem Fall.


Ja, genau so war es gemeint! Ich möchte, dass die vorhandene bedingte Formatierung dann auf die nächste Zeile übertragen werden soll, sobald ich eine neue Zeile einfüge.
Die Formatierung soll sozusagen immer in der gleichen Zeile bleiben

Bsp.

Bedingte Formatierung angewendet auf K9 ---> ich füge eine neue Zeile hinzu so wird aus K9 dann K10 ---> jetzt möchte ich das die bedingte Formatierung auf K10 (und nur da!), angewendet wird.

Ich habe mal eine Mustertabelle angefügt. Da wird schnell sichtbar, dass die Formatierung dann über mehrere Zeilen angewendet wird, das möchte ich vermeiden.

Bedingte Formatierung Makro Muster.xlsm

 

Hallo Christian,

wenn durch einfügen einer neuen Zeile das Ticket von Zeile 9 auf 10 gewandert ist, das das nun neue Ticket in Zeile 9 erledigt wurde, dann soll doch dort auch der erledigt-Status durch die Grüne Farbmarkierung hervorgehoben werden. bzw. soll ja dann auch für diese Zeile die Gelbe bzw. die Rote Markierung gesetzt werden.

Ansonsten müsstest du ja immer für jedes neue Ticket / neue Zeile die bedingte Formatierung nachträglich aktivieren. Das macht doch keinen Sinn.

Aus meiner Sicht arbeitet somit das Makro korrekt. Sicherheitshalber solltest du aber die K2-Abfragen durch $K$2 ersetzten, damit K2 fixiert ist.

 

Das Problem bei der aktuellen Version ist, dass wenn ich mehrere Datensätze hinzugefügt habe sich die Farben verändern.

screen 3.jpg

Kommt dardurch zu Stande, dass die Regeln auf mehrere Zeilen angewendet werden, wie auf dem Screenshot zu sehen ist. (Der Status ist auf offen, die Färbung aber rot und ganz unten sogar blank)

M. E. müsste in dem rotmakietren Bereich auf dem Screenshoot einfach nur =$K$10 stehen; für die nächsten 3 Farben dann =$K$11; usw. aber da weiß ich nicht wie man es realisiert.

 

Hi,

dann erweitere doch einfach den Bereich der Wirksamkeit für die 3 bedingten Formatierungen - nach diesem Prinzip:

Code:

 

Code:

 

Hallo Christian,

es geht viel einfacher! Nimm die bedingten Formatierungen komplett aus dem Makro raus und definiere dieser nur im Tabellenblatt.

Jetzt der kleine Trick dabei, Damit die Bereiche für die bedingten Formatierungen beim einfügen und löschen nicht verrückt spielen, definiere diese jeweils für den Bereich $K$8:$K:99999. Die Abfragen müssen dann entsprechend angepasst werden

Grün: =J8="erledigt"
Gelb: =UND(K8>$K$2;J8="offen")
Rot: =UND(K8>$K$2;J8="offen")

Das einfügen einer Zeile liegt somit immer innerhalb des Bereiches. Ich habe es bei mir getestet. Mehrere Zeilen eingefügt und gelöscht und es hat immer richtig angezeigt.

 

Vielen Dank!

Jetzt funktioniert es, so wie es soll, super!

*Smilie

 

Hallo Christian,

warum so kompliziert?
Das Problem ist, dass Du (warum auch immer) den Bereich, der die unteren beiden Zeilen abdeckt mit einer anderen Formel belegt hast, als Du es eigentlich wolltest (anstatt K2 steht dort K1048576). Dadurch können die auch nie so arbeiten, wie Du es gerne hättest.
Das ganze Neuanlegen von bedingter Formatierung bläht ausserdem nur den bedingten Formatbereich auf. Kannst Du Dir alles sparen, da die Formate beim einfügen einer Zeile automatisch übernommen werden.
Ich habe Deinen Code aus Modul3 in Modul1 kopiert und radikal gekürzt.
Nun funktioniert es wunderbar.

LG Ingo

 

Hi,

Das würde ich bleiben lassen, denn du belegst unnötig viele Zellen mit bedingter Formatierung, was überhaupt nicht notwendig ist. Sinnvoller ist es, den Bereich neu anzupassen - siehe meinen Beitrag von 29.07.2015 13:17

 

Hi Ingo,

auf Select kann verzichtet werden:

Code:

 

Hallo Beverly,

du kannst natürlich mit deiner Warnung durchaus recht haben. Ich selbst arbeite sehr häufig mit vielen Bedingten Formatierungen in Tabellen, deren Bereich ich eigentlich immer sehr großzügig definiere.

Dabei ist mir noch nie aufgefallen, dass die Tabellen besonders groß wurden, oder es besonders lange gedauert hat, dass bei Eingaben sich die Formatierungen angepasst haben.

Gibt es den irgendein Excel-Standardwerk, in dem man nachlesen kann, was man beachten bzw. vermeiden sollte, um erst gar nicht schlechte Antwortzeiten zu erhalten? wenn es so etwas gibt, dann würde ich es mir gerne kaufen. Leider ist aus meiner Sicht der Fachbuchmarkt aber unheimlich überschaubar, sobald man etwas tiefer einsteigen will. Selbst das "umfassende" Excel mit VBA Handbuch (Galileo / Bernd Held) beschriebt nur einen kleinen Teil dessen was möglich ist.

Auch Microsofts "Excel Formeln & Funktionen" ist zwar hilfreich aber bspw. bei der Erläuterung zum Spaltenindex von SVERWEIS "muss als positive Zahl auswertbar sein", muss man erst einmal drauf kommen, dass man hier somit also eine Formel einsetzen kann, um diesen dynamisch zu bestimmen.

Das meiste, von dem immer noch wenigen, das ich heute mehr als vor 2 Jahren weiß, musste ich mir bisher sehr mühsam erarbeiten. Ein Forum wie dieses ist dabei allerdings sehr hilfreich.

Meinem ehemals geliebten INDIREKT habe ich ja bereits die rote Karte gezeigt. Hier konnte ich auch tatsächlich beträchtliche Laufzeitunterschiede bei der Ausführung eines Makros feststellen, so wie auch dass allgemeine Handling mit dieser Arbeitsmappe nur wirklich sehr flüssig geworden ist.

 

Hallo Karin,

Danke für den Hinweis. Ich weiß das sogar, kann es aber häufig nicht anders umsetzen. In VBA habe ich nur Anfänger-Wissen. Aber hier für diese Aufgabe reichte es das ganze wirklich überflüssige zu entfernen. Der TS sollte ja auch seinen eigenen Code wieder erkennen können. Und von der Performance brauchen wir hier nicht zu reden.

Ich hätte mich mehr gefreut, wenn Du mich dafür gelobt hättest, was ich erkannt habe. Denn das hat doch alle anderen Lösungen für wirklich aufgebläht dargestellt. Da ist so ein aufgeblähtes "select" meinerseits doch unwichtig.

LG Ingo