Office: bedingte Formatierung beibehalten

Hallo liebe Helfer,

hat jemand eine Idee zu folgenden Problem:

Ich habe in verschiedenen Zellen bedingte Formatierungen hinterlegt und das Blatt so geschützt, dass man diese nicht über "Format" => "bedingte Formatierung" ändern kann, d.h. "bedingte Formatierung" ist blassgrau dargestellt.

Wenn ich jedoch einen Inhalt über Strg+V einfüge, den ich vorher in die Zwischenablage kopiert habe, dann ist meine bedingte Formatierung trotzdem weg.

Was kann ich tun? Mittels VBA schütze ich beim Öffnen mein Tabellenblatt wie folgt:

...
.Cells.Locked = True
.Range("A1:O1000").Locked = False
.Range("V15").Locked = False
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, Password:="Passwort"
...

Das Einfügen und Löschen von Zeilen und Spalten ist nicht möglich und soll auch nicht möglich sein. Mich verwundert dies jedoch, da der VBA-Code doch etwas anderes aussagt, oder...??? Kann ich das AllowInserting...bzw. AllowDeleting... weglassen?

Das nur nebenbei. Mein Hauptproblem ist die bedingte Formatierung, die es zerstört, wenn ich über Strg+V etwas einfüge. Ich könnte natürlich jedesmal über Bearbeiten=>Inhalte einfügen=>Werte gehen. Das ist aber zu umständlich. Ich möchte mich vor dem Strg+V schützen. Es soll also möglich sein, mittels Strg+V einzufügen ohne die bedingte Formatierung zu zerstören. In den Bereich A1 bis O1000 und in V15 soll das Einfügen und Löschen von Daten erlaubt sein. Filter müssen funktionieren. Spalten und Zeilen dürfen im gesamten Blatt weder eingefügt noch gelöscht werden.

Vielen Dank und LG
max

 

Hallo!

Mit Strg+v fügst du ALLES ein, sowohl Werte wie auch Formate.
Du könntest höchstens durch ein geeignetes Ereignis (Change, SelectionChange oder Calculate) die bedingte Formatierung nochmals wieder drüberschreiben.

 

Wahrscheinlich bin ich also gezwungen, bei jedem Öffnen der Datei über ein Makro die Formatierungen und bedingten Formatierungen und wieder herzustellen...

Na ich versuch mal ein derartiges Makro zu schreiben.
Gibt es da einen Trick, dass das relativ schnell geht? Meine Tabelle hat 1000 Zeilen und 26 Spalten und in 15 Spalten sind bedingte Formatierungen drin... Hilfe. Ich muss die dann in das Feld A2 einfügen und bis A1000 ausfüllen, in B2 einfügen und bis B1000 ausfüllen u.s.w.

Kannst du mir dafür ein Raster als Beispiel geben, vielleicht Folgendes für B2:B1000:

Format:
Zellschattierung hellgelb (der Rest bleibt)

bedingte Formatierung:
Bedingung 1: =ISTLEER(ZS(-3)) DANN Schriftfarbe rot,keine, keine Zellschattierung
Bedingung 2: Zellwert ist kleiner oder gleich ZS(-3) DANN Zellschattierung Farbe grün
Bedingung 3: Zellwert ist größer oder gleich 1,05*(ZS(-3)) DANN Zellschattierung Farbe rot

Dieses Format muss dann nach unten ausgefüllt werden bis B1000.

Ich danke vielmals!!!
LG

 

Hallo!

Zeichne es doch einfach mit dem Makrorekorder auf. Dann hast du den Code.

 

...aber der macht doch immer viel mehr als notwendig ist, oder??? und selectet dann jede einzelne Zelle..., anstatt irgend wie geschickt auszufüllen... hm.

 

Hallo!

Du hast doch jetzt schon eine Menge mit VBA gemacht. Da kannst du doch das Überflüssige wieder rausstreichen, wenn du weißt, dass "Select" überflüssig ist.
Mit
Range("B2:B1000").FormatConditions ...
sollte sich auch in der VBA-Hilfe bestimmt ein Beispiel finden lassen.

 

o.k. ich versuch´s mal. Wenn ich nicht klar komme, dann melde ich mich noch mal, o.k.?
LG
max

 

folgenden code habe ich geschrieben:

'Formatierungen und bedingte Formatierungen der Haupttabelle
Sub Formatierungen()
Application.ScreenUpdating = False
With Sheets("Haupttabelle(HT)").Range("A2:C1001")
.Interior.ColorIndex = 40
End With
With Sheets("Haupttabelle(HT)").Range("A2:A1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS)"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("B2:B1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-1))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("C2:C1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-2))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("D2:I1001")
.Interior.ColorIndex = 36
End With
With Sheets("Haupttabelle(HT)").Range("D2:D1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-3))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("E2:E1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-4))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("F2:F1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-5))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("G2:G1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-6))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("H2:H1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-7))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("I2:I1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-8))"
.FormatConditions(1).Interior.Pattern = xlNone
End With
With Sheets("Haupttabelle(HT)").Range("L2:L1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=ZS(-2)"
.FormatConditions(1).Font.ColorIndex = 3
End With
With Sheets("Haupttabelle(HT)").Range("M2:M1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISTLEER(ZS(-3))"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=ZS(-3)"
.FormatConditions(2).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="=1,05*(ZS(-3))"
.FormatConditions(3).Interior.ColorIndex = 3
End With
Application.ScreenUpdating = True
End Sub


Jetzt meine Frage: geht das auch irgend wie kürzer? Ich spreche doch immer wieder das selbe Sheet an. Ist das zu umständlich programmiert?
Außerdem habe ich noch ein Problem:
in der vorvorvororvorletzten, vorvorvorvorletzten und Zeile des Codes kommt ein Fehler: benanntes Argument nicht gefunden.
Also dort im Code:

.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="=1,05*(ZS(-3))"

Wenn ich den Code in dieser Sub ausführe klappt es, aber wenn ich ihn in eine andere einbinde, dann kommt diese Fehlermeldung.
Da weiß ich jetzt nicht weiter.
LG

 

Hallo!

Ich kenne mich mit der Z1S1-Bezugsart zwar nur recht spärlich aus, aber, anstatt für jede Spalte von A bis I die (gleiche!) Formatierung einzeln festzulegen, sollte doch wohl dies reichen, oder nicht?

With Sheets("Haupttabelle(HT)").Range("A2:I1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS1)"
.FormatConditions(1).Interior.Pattern = xlNone
End With

 

ja klar super. DANKE, da kann ich noch mehr verkürzen mit dem absoluten anstatt des relativen Zellbezuges.
Jetzt steht nur noch das Problem mit der Fehlermeldung...

 

Hallo!

Zum Fehler kann ich dir nichts sagen...
Wenn der Code in dieser Sub funktioniert, dann sollte er auch in einer anderen Sub funktionieren, sofern du alle nötigen Infos auch in der anderen Sub angibst.

 

sorry, dass ich noch mal nerve.
Ich habe den Code folgendermaßen komprimiert:

'Formatierungen und bedingte Formatierungen der Haupttabelle
Sub Formatierungen()

Application.ScreenUpdating = False

With Sheets("Haupttabelle(HT)").Range("A2:C1001")
.Interior.ColorIndex = 40
End With

With Sheets("Haupttabelle(HT)").Range("D2:I1001")
.Interior.ColorIndex = 36
End With

With Sheets("Haupttabelle(HT)").Range("A2:I1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS1)"
.FormatConditions(1).Interior.Pattern = xlNone
End With

With Sheets("Haupttabelle(HT)").Range("L2:L1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=ZS(-2)"
.FormatConditions(1).Font.ColorIndex = 3
End With

With Sheets("Haupttabelle(HT)").Range("M2:M1001")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ISTLEER(ZS(-3))"
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=ZS(-3)"
.FormatConditions(2).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=1,05*(ZS(-3))"
.FormatConditions(3).Interior.ColorIndex = 3
End With

Application.ScreenUpdating = True

End Sub


Dieser Code funktioniert in dieser Prozedur.
Kopiere ich ihn jedoch in eine Prozedur kommt der Laufzeitfehler '5': Ungültiger Prozeduraufruf oder ungültiges Argument.
Die Problemzeile:

.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=1,05*(ZS(-3))"

Ich verzweifle gerade... .

 

Hallo!

Ich kann nur raten...
Schreibe doch mal die 1,05 mit einem Punkt statt einem Komma, vielleicht bringt es ja was, auch wenn ich mir das eigentlich nicht vorstellen kann.

 

...habe ich schon probiert.
Wenn ich die Prozedur direkt aufrufe, dass heißt, im VBA-Editor direkt, dann funktioniert es erstaunlicherweise. Es handelt sich um eine Prozedur, die einem KlickButton auf einer UserForm zugeordnet ist. Wenn ich die Prozedur über VBA aufrufe, dann erscheint die UserForm, ich klicke auf den Button und es funktioniert.
Wenn ich jedoch die Datei schließe und erneut öffne, die UserForm erscheint, ich auf den Button klicke, dann kommt der Fehler.

Ich versteh das nicht.

So etwas hatte ich noch nie.

HILFE
Jetzt habe ich doch noch die Hoffnung, dass du mir helfen kannst.
Ich habe jetzt das ganze absolut adressiert und die 1,5* weggelassen.
Das führt dazu, dass die bedingten Formatierungen in Hochkommata stehen, z. B. ='ZS10'
Vielleicht ist da das Formula1 nicht gut geeignet. Er darf ja keine Hochkommata setzten.

p.s. habe es im Moment auf Excel2000 laufen. Vielleicht löst sich das Problem bei Excel2003 automatisch. Das kann ich aber erst morgen probieren. Meine einzige Hoffnung...
Trotzdem ganz vielen Dank. Wenn du noch eine Idee dazu hast, dann bin ich dir sehr dankbar.

 

Vermutung bestätigte sich:
In MS-Excel2003 läuft der Code.
:-D