Office: (Office 365) Bedingte Formatierung mit Zeitersparnis erstellen?

Hallo liebe Gemeinde,

ich habe eine Frage bezüglich der "bedingten Formatierung".

In meinem Kalender habe ich jeweils eine Spalte für meine Freundin und mich. Neben der jeweiligen Spalte (Person 1 und Person 2) ist jeweils noch eine Spalte, um dort mittels Buchstabenkombination das jeweilige Feld und das dazugehörige Feld aus der Spalte Person farblich zu markieren. Dies mache ich in der Regel über die bedingte Formatierung. Ich möchte nun aber nicht für 4 verschiedene Farben in 48 Spalten die bedingte Formatierung mit der Formel à la [=$C5="DR"] eintippen für die verschiedenen Bereiche definieren.

Gibt es eine intelligente / schnelle Variante die bedingte Formatierung in Abhängigkeit (siehe Bild Spalte C gehöhrt zu Spalte B und Spalte E gehört zu Spalte D) zu erzeugen?

 

Moin!
So etwas (alle Tage des Jahres) schreibt man ja auch untereinander und filtert bei Bedarf den angezeigten Monat.
Hilft auch bei Auswertungen ungemein!

Gruß Ralf

 

Moin Ralf,
danke für deine Antwort.
Ja, die Darstellung des Kalenders habe ich aus geschmacklichen Gründen so gewählt, damit wir die Monate entsprechend im Zweifel auch ausdrucken können. Praktikabel für Excel ist es wohl nicht zu 100%.
Aber dem Kommentar nach, ist die bedingte Formatierung wohl nicht so einfach möglich, oder? Ist wohl "fleißarbeit"...

Gruß Daniel

 

Ich schrieb ja bereits:
Dafür gibt es den Autofilter, der praktischerweise gleich einen Monatsfilter beinhaltet.

 

Über =TEILERGEBNIS() kann man trotz Autofilter im Titel auch den Monat wiedergeben, wie es hier auf dem Foto ist. Sogar die Anzahl Arbeitstage des Monats kann man damit dorthin summieren.

 

Obwohl ich meinen Vorrednern recht gebe, verstehe ich dein Anliegen. Mangels Beispieldatei nur das prinzipielle Vorgehen: Du brauchst je Code bzw. Farbe zwei Bedingungen. Zunächst markierst du eine Zelle einer Person, z.B. B5. Jetzt legst du eine bed.Form. an. Diese bekommt die Formel =C5="CR". Damit legst du die Farbe fest, die erscheinen soll, wenn die rechte Nachbarzelle den Wert "CR" hat. Jetzt erweiterst du "Bezieht sich auf" auf alle Personen-Spalten (B:B;D:D;...) Dasselbe Spiel machst du mit C5. Auch hier lautet die Formel =C5="CR". Hier wird die Farbe festgelegt, wenn diese Zelle den Wert "CR" hat. Der Bezug wird nun auf alle Code-Zellen (C:C;E:E;...) erweitert.

Wenn du eine gerade Zahl Spalten pro Monat hättest, also die 1.Person im Januar in Spalte B und im Februar in Spalte J und nicht I stehen würde, könnte man mit einer bed.Form arbeiten mit der Formel: =WENN(ISTUNGERADE(SPALTE());B5="CR";C5="CR")

 

Hallo,

ich habe dir das Makro "BedingtePersonenformate()" geschrieben. Es fügt auf Basis deiner angeführten Buchstaben in den Codespalten die bedingten Formate für die Zellhintergrundfärbung ein. Da du sinniger Weise die Spaltenbuchstaben weggeblendet hast, weiß ich allerdings nicht genau, wie viele Spalten du pro Monat verwendest - ich bin von 8 Spalten ausgegangen (also Spalte A:H für Januar, Spalte I:P für Februar, usw.), d.h. dass die Spalte für die WE/FT-Codierung als versteckte/ausgeblendete 8. Monatsspalte enthalten ist.
Außerdem habe ich die ärgerlichen Verbundzellen in den Monatsüberschriften entfernt und durch zentrierte Überschriften ersetzt.
Das Makro sieht dann wie folgt aus:

Code:

'Ermittelt die bedingten Formatierungen Januar-Dezember
'für Person1 bzw. Person2 auf Basis der Code-Spalten
'
Sub BedingtePersonenformate()
'
    Dim xSp As String, rngMonat As Range
    Dim Sp As Integer, Mo As Integer
  
    Set rngMonat = Range("$B$5:$C$35,$D$5:$E$35") 'Bed.Bereiche für Januar
    For Mo = 1 To 12
      For Sp = 1 To 2
         With rngMonat.Areas(Sp)
            xSp = "$" & Split(Split(.Address, ":")(1), "$")(1) 'Spaltenbuchstaben aus Bereichadresse ermitteln
            With .FormatConditions
              With .Add(Type:=xlExpression, Formula1:=FormelAnpassen("=ODER($C5='GT';$C5='U')", xSp))
                  .SetLastPriority
                  .Interior.Color = RGB(255, 192, 0)    'FF C0 00 - Orange
                  .StopIfTrue = False
              End With
              With .Add(Type:=xlExpression, Formula1:=FormelAnpassen("=ODER($C5='DR';$C5='A';$C5='SD')", xSp))
                  .SetLastPriority
                  .Interior.Color = RGB(0, 176, 240)    '00 B0 F0 - Blau
                  .StopIfTrue = False
              End With
              With .Add(Type:=xlExpression, Formula1:=FormelAnpassen("=($C5='P')", xSp))
                  .SetLastPriority
                  .Interior.Color = RGB(146, 208, 80)   '92 D0 50 - Grün
                  .StopIfTrue = False
              End With
              With .Add(Type:=xlExpression, Formula1:=FormelAnpassen("=($C5='O')", xSp))
                  .SetLastPriority
                  .Interior.Color = RGB(255, 255, 0)    'FF FF 00 - Gelb
                  .StopIfTrue = False
              End With
            End With '.FormatConditions
         End With 'rngMonat.Areas(Sp)
      Next Sp
      Set rngMonat = rngMonat.Offset(0, 8) 'Bed.Bereiche für Februar, März, ...: "$J$5:$K$35,$L$5:$M$35", "$R$5:$S$35,$T$5:$U$35", ...
    Next Mo
'
End Sub
'
'Ersetzt in "Formel" die Hochkommas ' durch doppelte Anführungszeichen "",
'Ersetzt in "Formel" die Spaltenbuchstaben $C durch einen neuen Spaltenbuchstaben Spalte
'
Private Function FormelAnpassen(Formel As String, Spalte As String) As String
   FormelAnpassen = Replace(Replace(Formel, "'", Chr$(34) & Chr$(34)), "$C", Spalte)
End Function

 

Hallo Exl121150,

vielen Dank für deine Hilfe! Das ist fantastisch!!!!

Sonnige Grüße

 

Ich musste deinen Code etwas anpassen, aber ansonsten funktioniert dieser wunderbar.
Da ich von MS immer eine Warnung erhalten habe, wenn ich deine Datei geöffnet habe, habe ich den Code einfach kopiert. Auch wenn der Code in deiner Datei einwandfrei funktionierte, bedurfte es einer Anpassung, damit es auch in meiner Datei funktioniert. Ich musste bei der letzten Private Function ein "Chr$(34)" und das "&" entfernen, so dass der Code nun wie folgt ausschaut.

Code:

Private Function FormelAnpassen(Formel As String, Spalte As String) As String
   FormelAnpassen = Replace(Replace(Formel, "'", Chr$(34)), "$C", Spalte)
End Function

Damit auch die farbliche Anpassung an den Wochenenden bzw. Feiertage umgesetzt wird, habe ich jeweils noch die Prorität auf

Code:

.SetFirstPriority

umgeschrieben.

Nochmals vielen Dank!

 

Hallo,
was die Funktion "FormelAnpassen(..)" betrifft, hast du natürlich recht. Ich bin hier auf die Heimtücke mit den Anführungszeichen hereingefallen.
Ich wollte im Makro die Formelausdrücke übersichtlicher gestalten, denn sonst hätte ich überall dort, wo in den Formelausdrücken des Makros jetzt ein einzelnes Hochkomma steht, jeweils 2 Anführungszeichen setzen müssen,
also statt FormelAnpassen("=ODER($C5='GT';$C5='U')", xSp)
hätte ich schreiben müssen: FormelAnpassen("=ODER($C5=""GT"";$C5=""U"")", xSp)
VBA hätte dann aus den je 2 Anführungszeichen ein einzelnes Anführungszeichen gemacht und hätte dieses dann im bedingten Format für Formeln gespeichert.
Da ich die Formeln jedoch über VBA manipulierte und in die endgültige Gestalt brachte, hätte ich die einzelnen Hochkommas nur durch je 1 Anführungszeichen ersetzen dürfen - du hast meinen Irrtum dankenswerterweise richtig erkannt und auch korrekt berichtigt. - Danke.

 

Hi,

kleiner Tipp: nimm irgendein anderes Zeichen, das in der Formel nicht vorkommt. Z.B. „#“.
Das nimmst du während der ganzen Manipulationen am String. Ganz am Ende machst du dann ein String = Replace(String, "#", Chr(34))