Office: Bedingte Formatierung über Makro

Hallo und guten Morgen alle zusammen.

Ich habe schon seit Jahren keine Hilfe mehr in Foren gesucht doch nun ist es mal wieder so weit dass ich mich mit einem neuen Thema befassen muss.

Und zwar Makro-Erstellung in MS-Excel.

Excel Version zum erstellen der Tabellen und Makros: Office 2013 Pro Plus

Excel Version wo das ganze laufen soll: Office 2007 Small Business


Ganz kurz zu meiner Problematik.
Unser Administrator hat "sehr hübsche" Tabellen gebaut und diese mit einem Blattschutz versehen.
Der neue Administrator, also ich, habe nun das Problem das ich seine Tabellen bearbeiten muss. Der Blattschutz ist kein Thema mehr der ist bereits entfernt. Mein eigentliches Problem sind die bedingten Formatierungen.

Der Ersteller des Tabellenblattes war der Meinung er muss hunderte von Bedingungen manuell ins Tabellenblatt schreiben. Leider ist es nun so das ich ausgerechnet die bedingten Formatierungen anpassen muss. Da sich namentlich was im Blatt geändert hat.

Ich möchte nun gern die gesamten Bedingten Formatierungen in ein Makro auslagern. Um sie später schneller und einfacher anpassen zu können.

Von Visual Basic und Excel Makros habe ich absolut keine Ahnung. Das ist das erste Mal das ich mit Excel Makros in Berührung komme. Mit Visual Basic habe ich vor Jahren mal angefangen, doch von dem Wissen ist nichts mehr da.

So nun endlich zu meiner Frage.

könnt Ihr euch mal das Tabellenblatt und den Makro-Code ansehen und mir sagen wie ich da am besten vorgehe.
Als Beispiel lade ich mal das original und die bereits bearbeitete Version hoch.

Ich bedanke mich schon mal für mögliche Hilfen, Tipps und Tricks.

Gruß
tomtom

:)

 

Hi tomtom,

da schaust Du hier oder hier

Um eine Übersicht Deiner bed. Formatierungen zu erhalten, kannst Du folgendes Makro mal laufen lassen:
Code:

 

Hallo R J,

danke für das Code-Snippet. Im Anhang habe ich mal die Debug.Print Ausgabe hinterlegt.

Bevor ich überhaubt weiter machen kann muss ich noch ein Frage loswerden. Was wird in Excel als Bereich (Range) definiert.

Ist die einzelne Zelle schon ein Bereich oder wird es wirklich nur das als Bereich angesehen wo ich sage von A3 bis Z3.

wenn ich mir den Ausgabe von Debug betrachte dann haben ja mehrere Zellen mehr als nur eine Formatierung. Ich bin gerade etwas verwirrt.

 

Ein Bereich (oder Range) kann sowohl eine einzelne Zelle (A4), ein zusammenhängender Bereich mehrerer Zellen (A4:C6), als auch ein loser Zellverbund (A1, A4,B3:C9) sein. Auch Cells(2,3) ist ein Range. [entspräche Range("C2")]

Das ist nichts ungewöhnliches. Wenn Person A anders als Person B formatiert werden soll, dann muss für jede Person eine eigene Regel erstellt werden. Das heißt, es muss eine neue FormatCondition mittels der Methode Add der FormatConditions Auflistung erstellt werden.
Schau Dir nochmal den letzten Link aus meinem letzten Post an.

Hinweis: Die FormatConditions sollten per VBA nur einmal erstellt werden! Solltest Du die entsprechende Routine über ein sich wiederholendes Ereignis aufrufen, dann unbedingt die bereits vorhandenen löschen. Andernfalls hast Du bald eine unüberschaubare Anzahl davon....*wink.gif*

Wenn Du noch herausbekommen willst, welche Formatierungen die jeweiligen Bedingungen haben, ändere mal die Debug Zeile so ab:
Code:

....ist jetzt nur ein Ausschnitt... gibt ja noch mehr Eigenschaften als nur Fettdruck oder, oder, oder.......*wink.gif*

PS:
Beachte auch den Unterschied zwischen dem FormatCondition Objekt (angesprochener Link) und der FormatConditions Auflistung (mit s am Ende).
In den Links sind alle Eigenschaften und Methoden aufgelistet.

 

Hallo R J,

ich habe mir die ganzen Links und API's mal angesehen. Es scheint eigentlich gar nicht so kompliziert zu sein wie es im ersten Moment aussieht.

Mich würde mal interessieren wo genau die Bedingten Formatierungen eingetragen werden.
Kommen sie in den Modul-Bereich oder in den Workbook-Bereich oder doch in das Tabellenblatt. Ich hätte ja fast gesagt in den Allgemeinen-Bereich (Tabellenblatt) wo auch die Zeit-Berechnung durchgeführt wird.

Wie ist eigentlich der generelle Ablauf also welcher Codebereich wird zuerst aufgerufen und abgearbeitet.

Gruß
tomtom

 

Wo, das ist im Prinzip vollkommen egal. Wichtig ist die korrekte Referenzierung.
Wenn Du den Code im Modul der Tabelle schreibst, auf die sich alles beziehen soll, und Du willst Dich auf A1 beziehen, dann genügt ein Range("A1")
Schreibst Du dagegen den Code in ein allgemeines Modul, dann musst Du noch die entsperechende Referenz mindestens auf die Tabelle
Bsp.: Tabelle1.Range("A1")
oder ggf. auch noch auf das Workbook setzen
Bsp.: ThisWorkbook.Tabelle1.Range("A1")

Machst Du das nicht, geht Excel immer vom aktuell aktiven Blatt des aktiven Workbooks aus. Das muss aber nicht zwangsläufig das sein, welches Du ansprechen wolltest.

Kommt darauf an. Ereignisprozeduren werden dann ausgeführt, wenn es auch eine entsprechende Prozedur gibt. Also, wenn mindestens der Prozedurrumpf im Code steht. Darin können aber auch eigene Prozeduren aus allgemeinen Modulen aufgerufen werden
Benutzerdefinierte Prozeduren in allgemeinen Modulen (oder auch in Tabellenmodulen), halt nur dann, wenn sie explicit angefordert werden. Die können wiederum Ereignisse auslösen, müssen aber nicht. Eine pauschale Aussage auf bestimmte Pozeduren kann man hier also nicht treffen.
Generell könnte man so sagen, der Ablauf erfolgt in Reihenfolge des Aufrufs. Unabhängig vom Ort des Aufrufs.

 

Hallo,

in einem Urlaubskalender der Abteilung sind einige bedingte Formatierungen hinterlegt, um Abwesenheitsarten, Ferien, Feiertage, Wochenenden und einiges mehr hervorzuheben. Da aber, wie bekannt, durch viele Copy/Paste Aktionen sich die Bereiche "Wird angewendet auf" immer wieder extrem aufblähen. wollte ich die Bedingten Formatierungen mittels Makro setzten und habe diese deshalb aufgezeichnet.

Für Datums und Zahlenformatierungen werden dabei aber Excel4 Makros aufgezichnet, die anschliessend aber den Laufzeitfehler 1004 ergeben.

Wie muss ich nun bspw. den Code

PHP:

         Selection.FormatConditions(1).StopIfTrue = False
Range("A1:AZ378").Select
Range("E17").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND(TAG(E17)=1;SPALTE()=1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
'------
ExecuteExcel4Macro "(2,1,""T.MMMM.JJJJ"")"
'------
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With


 

...hm... hat zwar jetzt nichts mit dem Ausgangspost zu tun.... aber sei's drum...

Hier geht es ja offensichtlich um das Format. Da bräuchtest Du kein Excel4 Makro. Könntest Du auch weglassen. Ich würde also vorher die Spalte ( oder den Bereich) und nicht das FormatCondition Objekt entsprechend formatieren.

 

Hallo Ralf,

wenn du dir die BF etwas genauer ansiehst, dann wirst du feststellen, dass das Format immer nur für den Ersten eines Monats gesetzt wird.

Und ich will ja auch kein Excel4 Makro, das nicht ausführbar ist. Aber der Recorder hat dies halt so aufgezeichnet. Und ich weiß halt nicht, wie ich das gewünschte Format der BF in VBA definieren muss.

 

....smile....

ja, ja.... der hat auch die Select und Activate Aufzeichnungen aufgezeichnet. Obwohl man die nicht braucht....*wink.gif*

wie gesagt, ich würde das Format nicht für die FC sondern für die Zelle(n), den Bereich festlegen. Denn, obwohl es zwar eine NumberFormat Propertie gibt, so ganz sicher, ob man die auch nutzen kann, bin ich da nicht. siehe hier

 

Hallo Ralf,

da war nur das Zahlenformat falsch.

Anstatt:

PHP:

         Range("A1:AZ378").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=UND(TAG(A1)=1;SPALTE()=1)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.NumberFormat = "d.mmmm.yyyy"
End With


 

Hallo RJ

die ganze Sache ist wohl doch schwieriger als ich dachte.

Entweder bekomme ich ständig Laufzeitfehler oder Objekt Eigenschaften existieren nicht.

Wenn ich den Zellbereich A8:C8 einfärben möchte dann färbt er nur A8 ein der Rest bleibt wie er ist.

Kann ich nicht irgendwie mit eine Suchen und Ersetzen Funktion Arbeiten. Ich möchte ja nicht alles neu machen.

Wenn ich zum Bsp. die xlsx in 7z öffne dann kann ich mir die styles und worksheet Dateien ansehen.

in der worksheet Datei finde ich auch an ca. 800 Positionen den Namen den ich ersetzen will. Allerdings finde ich in keiner Datei die Farbwerte die ich dank deines Snippets auslesen konnte.

Suche ich an der falschen Stelle oder muss ich wirklich alles im Code niederschreiben.

Gruß
tomtom

 

...was soll ich dazu sagen? Ich sehe ja nicht, was Du da gemacht hast. Dann ist vermutlich die Regel falsch formuliert oder was auch immer....

...ich auch nicht....*Smilie

Wenn Du nicht weißt, was Du da machst, solltest Du die Finger davon lassen und Dich lieber mit dem Code beschäftigen.

 

Macht natürlich kein Sinn ohne den Code zu Posten. Entschuldige.

Ich werde mich dann wohl oder Übels mit dem Code auseinander setzen müssen.