Office: Text bei bestimmten Bedingungen automatisch färben

Hallo Gemeinde,
also meine Frage bezieht sich auf folgendes Problem. Ich habe eine Art Kalender (alle Datumsangaben untereinander) Ich möchte, dass ich eine Art "Wenn/Dann- Funktion" habe in welcher ich definieren kann, dass das Wort So rot eingefärbt und Sa blau eingefärbt wird. Da gibt es doch bestimmt ne
Möglichkeit oder? Merci und bis denne. Gruß Sebastian

Achso, und noch eine "Kleinigkeit" fällt mir da ein. Wie geht es (oder geht es überhaupt) , dass ich unter jeder dieser untereinander liegenden Zellen (welche bereits mit Daten gefüllt sind) nochmal 5 Leerzeilen einfüge. In jede Zelle zu klicken , 5 nach unten zu markieren und dann Zellen einfügen klicken, wäre bei 365 Tagen etwas müßig. Ich hoffe mal ich habe mich halbwegs verständlich ausgedrückt. :-)

 

Hi Sebastian,

die Zelle färben bei erfüllter Bedingung kann man mittels bedingter Formatierung, aufzurufen über Format -> Bedingte Formatierung

Bis später,
Karin

 

Hallo Hossy81,

wie Beverly bereits schrieb geht das zum Beispiel über die Bedingte Formatierung.

Persönlich kenne ich mich damit nicht soo gut aus, und habe deshalb immer Schwierigkeiten, gleiche Bedingte Formatierungen in viele Zellen zu kopieren (wie es bei Dir ja zutrifft).

Also entweder mithilfe der Strg-Taste alle Samstage bzw. Sonntage markieren und die Schriftfarbe ändern (was Du vermutlich nicht möchtest, denn scheinbar möchtest Du Flexibilität), oder Du nimmst halt die bedingten Formatierungen, was entweder auch sehr viel Arbeit ist, oder es gibt einen Trick (den ich wie gesagt nicht kenne) um das ganze simpel zu kopieren.

Eine andere Möglichkeit wäre alles per Makro machen zu lassen.
Davon ausgehend das
- die Wochentage in Spalte A und ab der ersten Zeile stehen
- kein Blattschutz aktiv ist
- in Spalte A keinerlei anderen Bedingten Formatierungen benutzt wurden
- unter den Wochentagen auch nichts anderes mehr steht
habe ich Dir drei Makros geschrieben, mit denen Du die gewünschten Effekte erzielen kannst.


Dieses Makro schreibt die von Beverly bereits genannten Bedingten Formatierungen automatisch:

Code:

Option Explicit

Dim intLetzte As Integer

Dim rgZelle As Range


Public Sub SchriftfarbeBedingteFormatierung()
    
    With Me
        ' letzte nicht leere Zelle in Spalte A definieren
        intLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
        
        For Each rgZelle In Me.Range(.Cells(1, 1), .Cells(intLetzte, 1))
            With rgZelle
                .FormatConditions.Delete
                
                ' Samstag
                .FormatConditions.Add Type:=xlTextString, String:="Sa", TextOperator:=xlContains
                ' Schriftfarbe blau
                With .FormatConditions(1).Font
                    .Color = -65536
                    .TintAndShade = 0
                End With
                .FormatConditions(1).StopIfTrue = False
                
                ' Sonntag
                .FormatConditions.Add Type:=xlTextString, String:="So", TextOperator:=xlContains
                ' Schriftfarbe rot
                With .FormatConditions(2).Font
                    .Color = -16776961
                    .TintAndShade = 0
                End With
                .FormatConditions(2).StopIfTrue = False
                
            End With
        Next rgZelle
        
    End With
    
End Sub

Alternativ ändert dieses Makro einfach nur die Schriftfarbe:

Code:

Option Explicit

Dim intLetzte As Integer

Dim rgZelle As Range


Public Sub SchriftfarbeNormal()
    
    With Me
        ' letzte nicht leere Zelle in Spalte A definieren
        intLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
        
        For Each rgZelle In Me.Range(.Cells(1, 1), .Cells(intLetzte, 1))
            With rgZelle
                
                ' Samstag
                If rgZelle = "Sa" Then
                    ' Schriftfarbe blau
                    With .Font
                        .Color = -65536
                        .TintAndShade = 0
                    End With
                ' Sonntag
                ElseIf rgZelle = "So" Then
                    ' Schriftfarbe rot
                    With .Font
                        .Color = -16776961
                        .TintAndShade = 0
                    End With
                End If
                
            End With
        Next rgZelle
        
    End With
    
End Sub

Und dieses Makro fügt die von Dir gewünschten 5 Leerzeilen ein:

Code:

Option Explicit

Dim i As Integer
Dim intLetzte As Integer


Public Sub LeereZeilenEinfügen()
    
    With Me
        ' letzte nicht leere Zelle in Spalte A definieren
        intLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
        
        For i = intLetzte To 1 Step -1
            ' 5 Leerzeilen einfügen
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
        Next i
        
    End With
    
End Sub

Du könntest Dir also aussuchen ob eher Makro 1 (Bedingte Formatierungen) oder eher Makro 2 (ganz normal Schriftfarbe) besser für Dich geeignet wäre.

Wichtig ist nur:
- den Code ins Code-Modul des Tabellenblattes einfügen
- entweder Makro 1 ODER Makro 2 benutzen
- Makro 3 erst nachdem Du 1 oder 2 benutzt hast

Zur Veranschaulichung habe ich den Code in eine Beispielmappe eingefügt.


Ich hoffe ich konnte Dir ein wenig helfen.
Wenn noch etwas unklar sein sollte, Du weißt ja wo Du uns findest.

 

Alo erstmal vielen Dank an euch beide.
Beverly, dein Tipp ist für Excel Amateure wie mich sehr leicht verständlich und umsetzbar, so habe ich es dann auch ganz einfach hinbekommen.
Achim, dein Tipp ist wahrscheinlich für den Excel Profi auch sehr gut, dir natürlich auch besonders Dank für die viele Mühe. Vielleicht hätte ich jedoch noch schreiben sollen, dass ich mich mit dem ganzen Makros kein bischen auskenne (oft gehört und gelesen aber nie verstanden und angewendet).
Aber vielleicht kennt noch einer eine Lösung für mein 2. Problem.

Also die Tabelle wie Achim sie gemacht kommt ungefähr hin (ist etwas umfangreicher von den Spalten her). Aber in A stehen die ganzen Wochentage untereinander (insgesamt 365). Jetzt möchte ich zwichen jedem einezelnen Tag 5 Leerspalten einfugen, OHNE jedes mal anklicken 5 darunter liegende Zellen markiern und Zelle einfügen. Weil das müsste ich ja dann 365 mal tun und das wäre lästig und extrem zeitraubend. Gibt es da eine andere einfachere Lösung - möglichst ohne Makrodings :-)
Danke und Grüße

 

Hallo Hossy81,

in diesem Falle benötigst Du nur noch das 3. Makro.
Vergiss die anderen beiden.

Die Anwendung ist nicht schwierig, innerhalb einer Minute ist das Thema durch.
Habe Dir ein Bild angehängt.

Du öffnest Deine Excel-Datei und wählst das Blatt mit der Datumsliste aus.
Dann ALT - F11 drücken.
Damit öffnest Du den VBA-Editor (Makrodingsbums-Fenster). ;O)
(Alternativ den Reiter Entwicklertools auswählen und einen der beiden rot umrandeten Schalter drücken.)

Danach wählst Du im neuen Fenster Dein entsprechendes Arbeitsblatt mit der Datumsliste (bei mir findest Du das links).
Einmal Doppelclick darauf.

Danach diesen Code hier in den Editor kopieren:

Code:

Option Explicit

Dim i As Integer
Dim intLetzte As Integer


Public Sub LeereZeilenEinfügen()
   
    With Me
        ' letzte nicht leere Zelle in Spalte A definieren
        intLetzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count)
       
        For i = intLetzte To 1 Step -1
            ' 5 Leerzeilen einfügen
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
            Rows(i + 1).Insert Shift:=xlDown
        Next i
       
    End With
   
End Sub

Danach setze den Cursor vor " Public Sub" und drücke "Play" ganz oben in der Leiste.

Sobald das Makro durchgelaufen ist, kannst Du den einkopierten Code wieder löschen (alles markieren und "Entf") und den VBA-Editor schließen (Fenster von Makrodingsbums zumachen). ;O)

Jetzt kontrollierst Du ob Dir Deine Datumsliste so besser gefällt, speicherst...
und das wars.


Möglicherweise sieht Excel bei Dir etwas anders aus, kenne Deine Version ja nicht, und möglicherweise musst Du Makros (temporär) erst freigeben), ich kenne Deine Einstellungen ja nicht.

Wenn Probleme auftauchen sollten, oder etwas unklar ist, einfach nachfragen.

 

Danke Achim, das war nu auch für mich verständlich ;-)
Aber wenn ich das so mache, kommt bei mir folgende Meldung:
"Die Makros in diesem Projekt sind deaktiviert. Informationen zum Aktivieren der Makros finden Sie in der Online Hilfe oder der Dokumentation der Host Anwendung).

Vielleicht hilft es wenn ich noch mitteile, dass ich die Hossy81 Datei aus deinem vorherigen Post benutzt habe. Aber dann wirklich 100% so vorgegangen wie Du gerade beschrieben hast.

Zu meinem zweiten Problem, mache ich mal ein neues Thema auf.

 

Hallo Hossy,

öffne dein Excel (nicht eine Datei, sondern einfach nur Excel),
führe Schritt 1 bis 5 aus dem angehängten Bild durch.

Öffne danach Deine Kalenderdatei und führe Schritt 6 bis 8 durch.

Lasse danach das Makro laufen.

Da Du ja ansonsten wohl eher nichts mit Makros zu tun hast, Kannst Du danach Schritt 1 bis 4 wiederholen und in Schritt 5 die ehemalige Einstellung wieder auswählen.
(Wäre gut wenn Du sie Dir vorher merken würdest...)


Sollte dies immernoch nicht funktionieren... nochmal nachfragen, und vor allem genau beschreiben was nicht funktioniert, welches Excel Du hast usw.

 

Achim, nochmals bitte ich vielmals um Entschuldigung für meine Schusseligkeit (du weißt welche ich meine).
Aber jetzt mit Deiner Anleitung hat es geklappt. Dafür bin ich dir sehr dankbar, insbesondere für die echt supper schnelle, detaillierte und letzten Endes doch "Idiotensichere" Beschreibung :-)
Nochmals sorry und danke und ich hoffe ich kann weiter mit Hilfe rechnen (wenn ich Sie brauche)

 

Hallo Hossy81,

keine Sorge, wer fragt bekommt auch eine Antwort.

Freut mich das es nun doch noch geklappt hat.

 

Na gut, dann direkt noch 2 Fragen hinterher ;-)

1. wie kann ich denn das Makro von dir (Achim) speichern, so dass ich es evtl zu einem späteren Zeitpunkt bzw. einer anderen/ähnlichen Tabelle nochmal benutzen könnte?

2. Vielleicht kannst du mir noch folgendes beantworten. Ich füge mal die Beispiel Datei bei (quasi mein Fahrtenbuch von welchem ich die ganze Zeit spreche). So, wenn ich nun in dieser Datei ab Mo bis So und von Spalte A bis I alles markiere und dann nach unten ziehe (also quasi dieses automatische Fortsetzen) bildet Excel bei der Spalte mit den PLZ und bei der mit den km einen Mittelwert und kopiert diesen Zeile für Zeile anstatt die Werte aus der Markierung zu übernehmen. Bei der Spalte mit dem Ziel aber kopiert er es brav und setzt es in der Reihenfolge fort so wie ich es auch will. Welche Lösung gibt es denn dafür, falls es eine gibt?

Und Achim, nun verstehst du vllt. auch wieso ich die 5 Leerzeilen brauchte. Da ich nämlich im Außendienst bin und jeden Tag zu verschiedenen Zielen irre (mal 2, mal 3 aber max. 5) welche aber noch unter die Zeile Büro bzw. Außenstelle müssen (denn das ist immer der erste Anfahrtspunkt am Tag. Wobei die 5 Leerzeilen streng genommen nicht unter jeden Tag müssten, da ich Sa. und So. nicht arbeite, deshalb habe ich es dann nach Ausführen deines Makros (in der richtigen Tabelle) von Hand an diesen Stellen gelöscht. Falls du sonst noch irgend einen Verbesserungsvorschlag für diese Datei hast, nehem ich solche gerne entgegen ;-)
Viele Grüße

 

Hallo Hossy81,

ganz einfach, nicht aus dem Code-Modul des Blattes löschen, wo Du es rein kopiert hast.
Da diesem Makro kein "automatischer" Auslöser zugewiesen wurde, kann es ruhig dort bleiben ohne irgendwelchen Mumpitz zu machen.
Vorteil, Du musst später nicht suchen.

Jetzt wirds durcheinander und verstehe langsam nicht mehr wohin Du möchtest.

1. steht der Wochentag nun in Spalte B.
Besser sowas von vornherein sagen, denn nun müsste das Makro angepasst werden.

2. wozu willst Du irgendetwas "runter kopieren"?
Und was soll genau wohin?
Und wo sind die 5 Leerzeilen?
Wenn Du eine Beispieldatei zu Verfügung stellst, sollte sie aussagekräftig sein, damit wir nachvollziehen können was Du möchtest.
Übrigens besteht ein Unterschied zwischen "kopieren" und "runter ziehen".
Was von beidem möchtest Du?
Zusätzlich würde ich raten:
Zellen möglichst eindeutig zu formatieren, sprich Zahlen als Zahlen, Text als Text, PLZ als Postleitzahlen (findest Du unter "Sonderformat"), etc.

Musste ich erstmal nicht verstehen, Du wolltest sie. also bekamst Du sie.
Warum musst Du ja in erster Linie selber wissen.

Ich hoffe nicht. ;O)

Hättest Du das am Anfang gesagt, dann hätte das Makro dies auch nicht gemacht (ist kein Aufwand).
Von Hand hinterher entfernen find ich albern.



Ich habe das Gefühl, dass wir so nicht weiter kommen.
Bitte erstelle eine Beispieldatei (Zeitraum mindestens eine komplette Woche) mit allem drum und drann was Du benötigst.
Sollte ja nicht zuviel Arbeit sein, dies einmal von Hand zu machen.
Dann beschreibe bitte (möglichst präzise) alles was Du benötigst, und was nicht aus der Datei hervorgeht.
Und denke daran, keiner von uns kann wissen was Du denkst oder brauchst, also setze nichts voraus.

Danach können wir schauen.
Ich vermute mal, dass es gar nicht so viel Aufwand wird.

So ist mir das aber zu anstrengend.
Vor allem verstehe ich nicht mal die Hälfte.

 

Ok, ich mache es mal Schritt für Schritt. Wie bereits erwähnt sitze ich gerade an dem Fahrtenbuch für die Außendiensttätigkeit für das abgelaufene Jahr 2010. Diee neue Beispieldatei (angehängt) spiegelt eine Arbeitswoche dar. Um es ganz genau zu beschreiben (vgl. angehängte Datei "Beispiel 2"):

Am Mo. den 04.01.2010 bin ich zunächst ins Büro nach Entenhausen gefahren. Das waren von mir zu Hause aus 5 km. Von da aus bin ich weiter zum Kunden gefahren der in Koblenz wohnt. Von da asu zum nächsten Kunden der in Buch wohnt und zum letzten Kunden in Düsseldorf. Alles in allem habe ich (fortgeschreiben) an diesem Tag 117 km zurückgelgt.

Am Di den 05.01.2010 bin ich zunächst ind die Außenstelle nach Musterstadt gefahren. Das sind von mir zu Hause aus 42 km. Von da gings weiter zum Kunden nach Bremberg etc etc etc.

Jetzt wird deutlich wie so eine Woche im Außendienst aussieht. An einem Tag habe ich mal 4 Termine an einem anderen 5 Termine mal nur zwei Termine aber immer max. 5 am Tag. Variiert halt von Tag zu Tag.
Das Grundgerüst (siehe angehängte Tabelle "Beispiel 3") für das jährliche Fahrtenbuch ist jedoch jedes Jahr immer das selbe . Bestehend aus den 365 Tagen des Jahres (Spalte B und C) dem Ziel, der PLZ, dem Ort, der Adresse, der fortgeschriebenen km pro Arbeitstag in den Spalten D bis H.
Ebenfalls fängt jeder Mo. und jeder Freitag im Büro in Entenhausen an und jeder Di, Mi und Do. fängt mit der Außenstelle in Musterstadt an.
Die Entfernung dort hin von zu Hause kenn ich natürlich und trage ich auch schon ein. Ins Büro nach Entenhausen sind es 5km und in die Außenstelle sind es 42 km. So das sind alle Grunddaten die jede Woche, jedes Jahr gleich sind. Daraus will ich jetzt schon (wo ich einmal dran bin) bereits das Fahrtenbuch für die kommenden Jahre erstellen. Denn diese Grunddaten kenne ich ja alle schon. Da ich aber im Vorfeld noch nicht weiß wieviele Termine ich in der Zukunft habe, wollte ich unter jedem Tag die 5 Leerzeilen haben, da es ja wie bereits erwähnt max 5 Termine pro Tag sein können. Wenn ich dann nach dem Arbeitstag weiß, weiviele Termine ich hatte und es waren z.B. nur 3, dann lösche ich halt die anderen beiden Leerzeilen von den insgesamt 5 wieder. Am Ende sieht es dann so aus wie in der "Beispiel 2" - Datei.

Sollte es tatsächlich eine noch einfachere Lösung geben, bin ich für Tipps dankbar.

Also nochmal zusammenfassend. Die "Beispiel 2" Datei ist eine fertige Woche meines Fahrtenbuches. zur Veranschaulichung wie das Ganze mal fertig aussieht. . Die "Beispiel 3" Datei ist das was dem fertigen Fahrtenbuch vorausgeht. Nämlich das besagte Grundgerüst welches immer gleich ist.

Und jetzt nochmal zu dem Hauptproblem mit dieser "Beispiel 3" Datei: Schau mal in die Spalte E (PLZ) und die Spalte H (km). In der 1. KW habe ich die korrekten Werte eingetragen. Dann habe ich alles markiert und nach unten gezogen um einen "Fortlauf"des Ganzen zu haben. Aber was macht Excel bitte mit der Spalte E (PLZ) und der der Spalte H (km)??? Er bildet einen Mittelwert obwohl ich die PLZ auch als PLZ im Format angegeben habe. Das andere alles, also die Wochentage, das Datum , das Ziel etc. macht Excel so wie ich es haben will.

Ich hoffe das war nu ein bischen verständlicher, irgendiwe zweifel ich noch ob es das wirklich war :-)

 

So hier noch die andere Beispiel Datei mit dem "Grundgerüst"

 

Hallo Hossy81,

ich denke jetzt ist es verständlicher.
Du möchtest die immer gleichen Werte von vornherein eingetragen haben um Dir manuelle Arbeit zu sparen.

Das ist kein Problem.
Könnte man zum Beispiel auch per Makro, von Anfang an, direkt fürs ganze Jahr machen.

Für Urlaubs- Krankheits- und Feiertage hättest Du halt ein paar "unnütze" Einträge, die von Hand wieder zu entfernen wären.


Was mir persönlich an Deiner Datei fehlt: Summen

Brauchst Du keine Tages- Wochen- Monats- Jahressummen der Kilometer und der Überstunden?

Die Zeilen bzw. Zellen dafür sollte man direkt am Anfang einplanen, damit es hinterher kein gemurkse wird.

Sach mir bitte ob Du solche Summen brauchst und ob ab Spalte J noch irgendwelche Einträge kommen (wenn ja welche) oder alles leer bleibt.


Ich schau schon mal wegen dem Rest.


Eins noch, brauchst Du für jedes Jahr ein eigenes Blatt, oder wäre eine Datei mit einem Blatt für jedes Jahr auch eine Option?

 

Mit einem Satz den Nagel auf den Kopf getroffen. :-)
Ich könnte dir per PN mal mein Fahrtenbuch aus einem der letzten Jahre (z.B. 2009) schicken. Dann siehst du wie ich es mit Urlaub, Krankheit bzw. Feiertagen gehandelt habe. Im Prinzip habe ich das aber immer so gemacht wie es auch aus der Beispiel Datei 2 ersichtlich ist. Dort kannst du ja sehen, dass ich z.B. am 01.01.2010 nur Striche habe, genauso wie am Wochenende. Bei Urlaub und Krankheit habe ich das Wort "Krankheit" bzw. "Urlaub" in die Spalten D, F und G reingeschrieben.

Zu den Summen: Da habe ich es immer folgendermaßen gemacht: Ich muss die Fahrten zum Büro und zu der Außenstelle als sog. "Fahrten zur Arbeitsstätte" von den Fahrten zum Kunden trennen. Also habe ich (wenn das ganze Jahr fertig war) zunächst die Spalte H mit den km markiert und per Autosumme ausrechnen lassen. Dann habe ich per Suchfunktion das Wort "Büro" und das Wort "Außenstelle" gesucht. Mal angenommen die Summe der beiden gefundenen Begriffe habe ich dann einfach mit der km-Entfernung des entsprechenden Zeil multipliziert. Also mal angenommen Das Wort Büro wurde von Excel 80 mal gefunden und ich weiß ja, dass das Büro 5km entfernt liegt. So habe ich 80 * 5 gerechnet und weiß dann, das ich 400 km insgesamt zum Büro gefahren bin. Gleiches Mache ich mit der Außenstelle und ziehe die beiden Ergebnisse von dem Gesamtergebnis (was mir Excel vorher per Autosumme berechnte hat) ab. So kann ich dem Finanzamt sagen, vieviel Fahrten bzw. km ich zur ständigen Arbeitststätte (Büro und Außenstelle) gefahren bin und wieviel km ich für "Fahrten für die Außendiensttätigkeit" hatte (Wird nämlich gesondert berechnet vom Finanzamt). Aber Wochen oder Monatssummen braucht das Finanzamt nicht (ich auch nicht), denn die rechnen immer nur für 1 Jahr ab.

Die Spalte J wird nicht benutzt. Zur Erklärung der Spalte I (weil die wird genutzt): Hier mache ich einfach ein "Häkchen-Symbol" rein an den Tagen an denen ich länger als 8 Stunden ununterbrochen im Außendienst war (gibt dann noch ein Extra-Zuschlag).

Ich habe bislang immer für jedes Jahr eine eigene Datei gemacht.

Lass mich noch wissen, wie ich dir das angesprochene Fahrtenbuch aus dem letzten Jahr zukommen lassen kann, damit du es mal siehst. Vielleicht ergeben sich hieraus noch mehr Verbesserungsvorschläge deinerseits. Ist nämlich super viel Arbiet das zu machen. Und meistens bin ich zu faul und muss dann die Daten von einem Jahr nachtragen. Da ich aber dann das Grundgerüst schon habe, muss ich ggf. nur noch die Außendiensttermine eintragen. Aber gut für 2010 habe ich das "Gerüst dank deiner Hilfe mit dem Makro schon uns muss jetzt auch nur noch nie Termine eintragen :-)

Vorab schonmal super vielen Dank für die bis hierher geleistete Hilfe.