Office: Zellen färben mit VBA - gelöst

Hallo,

ich habe jetzt schon mehrfach einen Code verwendet, den ich mal im Internet gefunden habe, um Zellen zeilenweise abwechselnd hellgrau und weiß einzufärben.

Eigentlich funktioniert der Code.
Ich kann sogar Zeilen einfügen, und die Färbung ändert sich automatisch (von sich aus) entsprechend in der neuen und allen nachfolgenden Zeilen, ohne das ich ihn aufrufe.

Das Problem:
irgendwann produziert der Code nur noch Fehlermeldungen.
Das kann nach ein paar Durchläufen, oder auch erst nach mehreren Monaten sein.
(Habe den Code in mehreren Dateien eingesetzt, wenn sich Bereiche durch neue Einträge dauernd erweitern.)
Ab dann musste ich den Code immer deaktivieren.
Meistens funktionierte die Färberei trotzdem weiterhin wie gewünscht.

Aber halt nicht immer.
Und mittels eines anderen Codes (.Color = .. oder . ColorIndex = ... ) die Zellen zu färben, scheint schon die einmalige Verwendung dieses Codes zu verhindern.


Da ich die Begriffe und Funktionen "hinter" dem Code nicht genau kenne, kann ich nur vermuten:

Code:

.FormatConditions.Add Type:=xlExpression, Formula1:="=ZEILE()=GERADE(ZEILE())"

Mir scheint, hier wird eine Formel in eine Zelleigenschaft geschrieben, und dadurch irgendwie die nachträgliche Veränderung dieser Eigenschaft verhindert.


Da es irre aufwendig wäre in mehreren Dateien die Daten zu kopieren, in neue (ungefärbte) Zellen einzufügen und die gefärbten zu löschen, suche ich nach einer Möglichkeit diese "Formel" in den Zelleigenschaften zu entfernen und die Zellen danach ganz normal zu färben.

Ich hoffe jemand weiß Rat.


Hier noch der komplette Code:

Code:

'Zeilen einfärben
            'For i = 7 To letzteZeile + 2
                'With Range(Cells(i, 1), Cells(i, 8))
                'ungerade Zeilen grau einfärben
                    '.FormatConditions.Add Type:=xlExpression, Formula1:="=ZEILE()=UNGERADE(ZEILE())"
                    '.FormatConditions(.FormatConditions.Count).SetFirstPriority
                    'With .FormatConditions(1).Interior
                        '.PatternColorIndex = xlAutomatic
                        '.ThemeColor = xlThemeColorDark1
                        '.TintAndShade = -0.1
                    'End With
                    '.FormatConditions(1).StopIfTrue = False
                'gerade Zeilen weiß einfärben (keine Füllung)
                    '.FormatConditions.Add Type:=xlExpression, Formula1:="=ZEILE()=GERADE(ZEILE())"
                    '.FormatConditions(.FormatConditions.Count).SetFirstPriority
                    'With .FormatConditions(1).Interior
                        '.PatternColorIndex = xlAutomatic
                        '.ThemeColor = xlThemeColorDark1
                        '.TintAndShade = 0
                    'End With
                    '.FormatConditions(1).StopIfTrue = False
                'End With
            'Next i

 

Hallo Achim,

warum machst Du es nicht ohne VBA, mit bedingter Formatierung?
http://hajo-excel.de/gepackt/ohne_vba/farbe_jede_2_andere_farbe.htm

Gruß Hajo

 

Hallo Hajo,

weil ich damals, als ich diesen Code suchte und fand, nicht wusste wie ich den Bereich, für den diese Formatierung gelten soll, einschränke bzw. dynamisch erweitere.

Ein Beispiel:
Die ersten 5 Zeilen sind der Blattkopf, dort habe ich Überschriften, sonstige Infos etc. und sie sind fixiert.
Ab Zeile 6 folgen dann laufend die neuen Einträge.
Pro Eintrag halt jedes mal eine neue Zeile.

Natürlich könnte ich die Bedingte Formatierung auf eine ganze Spalte, oder den Bereich "A6:A50000" anwenden, aber da hatte ich mich aus 2 Gründen gegen entschieden:
1. erweitert sich der Druckbereich bei meiner Lösung dynamisch von selber, ohne das ich etwas tun muss. Mit der Bedingten Formatierung sind dagegen alle Zeilen bis sonst wohin gefärbt und wollen dann auch gedruckt werden.
2. entweder verlangsamt die Bedingte Formatierung Excel sehr stark, sobald sehr viele Zellen/Zeilen formatiert sind, oder die Dateigröße wird sehr stark aufgebläht.

Beide Gründe habe ich so in Erinnerung, ob und was davon wirklich stimmt, weiß ich nicht mehr.

Im Endeffekt lief es damals darauf hinaus:
Ich = Anfänger > Code funktionierte > Bedingte Formatierung habe ich nicht hinbekommen > also nahm ich Code
Und seitdem habe ich eine bestehende Lösung benutzt.
leider, denn sie macht immer mehr Mucken.

 

Hallo Achim,

ich vermute da liegst Du falsch, Dein Code macht es auch über bedingte Formatierung. Ich müßte mich sehr irren wenn

nicht bedingte Formatierung ist.

Gruß Hajo

 

Ich vermute da liegst Du richtig... ;O)

Das würde mir so einiges erklären!

Jetzt bräuchte ich nur noch einen Code (oder was auch immer) um erstmal die ganzen Formatierungsformeln wieder weg zu bekommen.

danach schaue ich dann weiter.

 

Hallo Achim,

Register Start, Befehlsgruppe Formatvrlage, bedingte Formatierung, Regeln lösche, alle

Gruß Hajo

 

Jo!
War exakt der richtige Tipp.
Der Code hat wirklich Bedingte Formatregeln geschrieben.

Habe sie dort alle gefunden - und gelöscht.

Wenn Du wüstest was mir ein Stein auf den Fuß geplummst ist...
Ganz lieben Dank.

Werde das erstmal bei allen "Problemfällen" machen, und dann in Ruhe überlegen wie ich es in Zukunft mache.

 

Hallo Achim,

vielleicht so für gesamte Spalte
=UND(ANZAHL2(A1:N1)>0;Zeile()>5;REST(ZEILE();2)=0)

Gruß Hajo

 

Lieben Dank Hajo,

aber Bedingte Formatierungen möchte ich möglichst vermeiden.
Per Code kann ich das so steuern, aufrufen etc. wie ich es brauche.

Und jetzt das nächste Problem:

"Anwendungs- oder Objektdefinierter Fehler."

Code:

    With Workbooks(wkb2).Worksheets(tbName)
        letzteZeile = .Range("G2").Value + 1
    'Zeilen einfärben
        For i = 10 To letzteZeile + 1
            If i Mod 2 = 0 Then     'Rest der Division durch 2 = 0
                'gerade Zeilen weiß einfärben (keine Füllung)
                With .Range(Cells(i, 1), Cells(i, 10)).Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            Else
                'ungerade Zeilen grau einfärben
                With .Range(Cells(i, 1), Cells(i, 10)).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.1
                    .PatternTintAndShade = 0
                End With
            End If
        Next i
    End With

Die bedingten Formatierungen habe ich alle gelöscht.
Der Schutz von Workbook und Worksheet ist definitiv aufgehoben.

Was mache ich denn jetzt noch falsch?
(in einem anderen Modul lief genau der gleiche Code gestern Abend problemlos)

 

Hi Achim,

in welcher Zeile kommt denn der Fehler?

Bis später,
Karin

 

Sorry, ganz vergessen...

 

Hi Achim,

vor Cells fehlt in 4 Fällen der Punkt.

Bis später,
Karin

 

*gnarf*
die Kleinigkeiten halt...

Dankeschön, jetzt klappts.


Hm, dazu aber noch eine Frage:
In einem anderen Code (Sortierfunktion) fehlen die Punkte auch.
Diesen Code habe ich schon oft benutzt und noch nie irgendwelche Probleme gehabt.
Müssen die Punkte zum referenzieren immer dorthin und ich habe (zufällig) Glück gehabt?
Oder müssen diese Punkte nur in bestimmten Fällen dorthin?

Code:

    With Workbooks(wkb1).Worksheets(wks2)
        letzteZeile = Worksheets(wks2).Cells(3, 2).Value + 1
        Worksheets(wks2).Sort.SortFields.Clear
        Worksheets(wks2).Sort.SortFields.Add Key:= _
            Range(Cells(7, 15), Cells(letzteZeile, 15)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortTextAsNumbers
        Worksheets(wks2).Sort.SortFields.Add Key:= _
            Range(Cells(7, 1), Cells(letzteZeile, 1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortTextAsNumbers
        With Worksheets(wks2).Sort
            .SetRange Range(Rows(7), Rows(letzteZeile))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

 

Hi Achim,

der Fehler tritt auf, wenn innerhalb eines Befehls unterschiedliche Bezüge genommen werden (wie in dem geposteten Beispiel mit dem Laufzeitfehler - mal mit und mal ohne Punkt), wobei es meines Wissens so ist, dass die verschiedenen Excel-Versionen auch unterschiedlich reagieren, sodass der Befehl in diesen Versionen fehlerlos abgearbeitet wird. Möglicherweise hängt das auch davon ab, in welchem Tabellenblatt man sich gerade befindet und auf welches Tabellenblatt sich der Code bezieht - das kann ich jedoch nicht mit Bestimmtheit sagen.

Generell ist es so, dass der Punkt immer dann gesetzt wird, wenn mit With der Bezug auf ein bestimmtes Objekt (hier Tabellenblatt) genommen wird. Man kann natürlich auch Bezüge mit und Bezüge ohne Punkt innerhalb einer Befehlszeile verwenden - je nachdem, auf welches Objekt sich im betreffenden Befehl bezogen wird, wobei der Bezug ohne Punkt immer auf das gerade aktive Tabellenblatt gerichtet ist.

In dem Code aus deinem letzten Beitrag kannst du innerhalb von With Workbooks(wkb1).Worksheets(wks2)... End With an allen Stellen Worksheets(wks2) weglassen, da du mit der ersten Codezeile das Bezugsobjekt ja bereist festgelegt hast. An den Stellen, wo du keinen Punkt verwendest, wird Bezug auf die aktive Tabelle genommen, was korrekt ist, wenn du dich tatsächlich auf diese beziehen willst. Ein Fehler wird dabei nicht ausgelöst, könnte aber unter Umständen in zu einem falschen Ergebnis führen.

Bis später,
Karin

 

Lieben Dank für die Erklärung, Karin.
Das ich mir so einige "Worksheets(wks2)" eigentlich sparen kann, war mir klar, bzw. bin ich von ausgegangen.
Allerdings hatte ich dann in der Vergangenheit mehrfach Probleme, vermutlich weil ich irgend etwas nicht ordentlich Referenziert habe (Punkt vergessen etc.) und habe dann irgendwann aus "Übervorsicht" lieber den Bezug ausgeschrieben.

Habs aber gerade mehrfach getestet, der folgende Code läuft problemslos und fehlerfrei:

Code:

    With Workbooks(wkb1).Worksheets(wks2)
        letzteZeile = .Cells(3, 2).Value + 1
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:= _
            .Range(.Cells(7, 15), .Cells(letzteZeile, 15)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortTextAsNumbers
        .Sort.SortFields.Add Key:= _
            .Range(.Cells(7, 1), .Cells(letzteZeile, 1)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortTextAsNumbers
        With .Sort
            .SetRange Range(Rows(7), Rows(letzteZeile))
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

Was mich jetzt nur etwas verwirrt ist diese Zeile:

Code:

.SetRange Range(Rows(7), Rows(letzteZeile))

Dort fehlen die Punkte, und Excel meckert auch sobald ich da welche setze.
Weder:

Code:

.SetRange .Range(.Rows(7), .Rows(letzteZeile))

noch

Code:

.SetRange Range(.Rows(7), .Rows(letzteZeile))

noch

Code:

.SetRange = .Range(.Rows(7), .Rows(letzteZeile))

oder eine ähnliche Schreibweise funktionieren.

Jedesmal wird die Methode nicht unterstützt.
Dürfen da die Punkte nicht?
Oder habe ich nur bisher die korrekte Schreibweise nicht gefunden?