Bedingte Formatierung

[WillWissen]

Hi schadre,

Kurios für mich ist weiterhin, dass wenn ich "01.12.2014 - 30.01.2015" eingeben, er den Wert 01.12.2020 in der Hilfszelle ausgibt und dann nicht einfärbt.

da hast du wohl nicht das Format TT.MM.JJ gewählt. Siehe Beitrag #2 v. 13.12.2014, 22:33

Wichtig ist aber, dass deine Daten immer im selben Format, z.B. TT.MM.JJ, geschrieben werden.

und Beitrag #15 v. 14.12.2014, 15:33

Sollte das Format TT.MM.JJJJ gewünscht sein, muss lediglich die Formel

... LÄNGE(I9));8)*1;"") auf 10

geändert werden. Aber das wäre dann eine einmalige Anpassung.

An deinem anderen Problem arbeite ich.

[atilla]

Hallo zusammen,

Wenn VBA eingesetzt werden kann, dann wäre eine VBA Lösung einfacher umzusetzen.

Unten ein VBA Code, der in Spalte I ab Zeile 9 die Zellen, die ein Datum enthalten, rot färbt.
Folgenden Code in das Code Fenster der betreffenden Tabelle einfügen:

Code:

Private Sub Datum_kleiner_groesser()
   Dim i As Long, j As Long, lngZ As Long
   Dim arr
   lngZ = Cells(Rows.Count, 9).End(xlUp).Row
   Range("I9:I" & lngZ).Interior.ColorIndex = xlNone
   For j = 2 To lngZ
      If Cells(j, 9) <> "" Then
         arr = Split(Cells(j, 9))
         If UBound(arr) >= 0 Then
            For i = UBound(arr) To LBound(arr) Step -1
               If IsDate(arr(i)) Then
                  If CDate(arr(i)) < Date Then
                     Cells(j, 9).Interior.ColorIndex = 3
                  End If
                  i = LBound(arr)
               End If
            Next i
         End If
      End If
   Next j
End Sub

Die Bedingte Formatierung für die Spalte I löschen.
Den Cursor in eine Codezeile setzen und die Funktionstaste F5 drücken.
Jetzt müssten die entsprechenden Zellen rot gefärbt sein.

Damit das zukünftig automatisch passiert, folgenden Code zusätzlich in das Code Modul der betreffenden Tabelle einfügen:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 9 And Target.Row > 8 Then
      On Error GoTo fehler
      Application.EnableEvents = False
      Application.ScreenUpdating = False
      Datum_kleiner_groesser
   End If
fehler:
   Application.EnableEvents = True
   Application.ScreenUpdating = True
   If Err Then MsgBox "FEehler: " & Err.Number & vbLf & vbLf & Err.Description
End Sub

Dieser Code reagiert auf Eingaben in der Spalte I ab Zeile 9

[WillWissen]

Hi,

nachdem dies deine Vorgabe ist

Bei deiner ersten Formel vom 13.12.2014, 22:33, hat er die Zelle nicht eingefärbt, da der zweite Wert ja noch in der Zukunft liegt.
So hätte ich das wenn möglich auch weiterhin. Bitte, bitte.....

meine Formellösung:

Tabelle3

	I	J	K	L
9	möchte ab 01.03.15 in Filiale A arbeiten	01.03.2015	01.03.2015	zukünftig
10	Urlaub von 15.1.15 - 10.02.15	15.01.2015	10.02.2015	zweites Datum zukünftig
11	Seminar 01.01.15 - 20.01.15	01.01.2015	20.01.2015	beide Daten Vergangenheit
12	ab 15.01.15 in 3. Abtlg.	15.01.2015	15.01.2015	Datum Vergangenheit + 3. Abtlg. hinten
13	Urlaub 01.01.15 - 22.1.15	01.01.2015	22.01.2015	erstes Datum Vergangenheit, zweites heute
14	3. Abtl. bis zum Urlaub am 13.12.14		13.12.2014	Vergangenheit + 3. Abtlg. vorne
15	3. Abtl. bis zum Urlaub am 1.02.15		01.02.2015	Datum zukünftig + 3. Abtlg. vorne
16	Meier 2. Abmahnung	02.01.1900		kein Datum

Formeln der Tabelle

Zelle Formel J9 {=WENNFEHLER(LINKS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8)*1;"")} K9 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8))*1;WENNFEHLER(TEXT(TEIL(I9;FINDEN("-";I9)+2;8);"TT.MM.JJJJ")*1;J9))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I9;MAX(ISTZAHL(TEIL(I9;SPALTE(9:9);1)*1)*SPALTE(9:9)));VERGLEICH(1;ISTZAHL(TEIL(I9&0;SPALTE(9:9);1)*1)*1;0);LÄNGE(I9));8))*1;WENNFEHLER(TEXT(TEIL(I9;FINDEN("-";I9)+2;8);"TT.MM.JJJJ")*1;J9)))} J10 {=WENNFEHLER(LINKS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8)*1;"")} K10 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8))*1;WENNFEHLER(TEXT(TEIL(I10;FINDEN("-";I10)+2;8);"TT.MM.JJJJ")*1;J10))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I10;MAX(ISTZAHL(TEIL(I10;SPALTE(10:10);1)*1)*SPALTE(10:10)));VERGLEICH(1;ISTZAHL(TEIL(I10&0;SPALTE(10:10);1)*1)*1;0);LÄNGE(I10));8))*1;WENNFEHLER(TEXT(TEIL(I10;FINDEN("-";I10)+2;8);"TT.MM.JJJJ")*1;J10)))} J11 {=WENNFEHLER(LINKS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8)*1;"")} K11 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8))*1;WENNFEHLER(TEXT(TEIL(I11;FINDEN("-";I11)+2;8);"TT.MM.JJJJ")*1;J11))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I11;MAX(ISTZAHL(TEIL(I11;SPALTE(11:11);1)*1)*SPALTE(11:11)));VERGLEICH(1;ISTZAHL(TEIL(I11&0;SPALTE(11:11);1)*1)*1;0);LÄNGE(I11));8))*1;WENNFEHLER(TEXT(TEIL(I11;FINDEN("-";I11)+2;8);"TT.MM.JJJJ")*1;J11)))} J12 {=WENNFEHLER(LINKS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8)*1;"")} K12 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8))*1;WENNFEHLER(TEXT(TEIL(I12;FINDEN("-";I12)+2;8);"TT.MM.JJJJ")*1;J12))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I12;MAX(ISTZAHL(TEIL(I12;SPALTE(12:12);1)*1)*SPALTE(12:12)));VERGLEICH(1;ISTZAHL(TEIL(I12&0;SPALTE(12:12);1)*1)*1;0);LÄNGE(I12));8))*1;WENNFEHLER(TEXT(TEIL(I12;FINDEN("-";I12)+2;8);"TT.MM.JJJJ")*1;J12)))} J13 {=WENNFEHLER(LINKS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8)*1;"")} K13 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8))*1;WENNFEHLER(TEXT(TEIL(I13;FINDEN("-";I13)+2;8);"TT.MM.JJJJ")*1;J13))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I13;MAX(ISTZAHL(TEIL(I13;SPALTE(13:13);1)*1)*SPALTE(13:13)));VERGLEICH(1;ISTZAHL(TEIL(I13&0;SPALTE(13:13);1)*1)*1;0);LÄNGE(I13));8))*1;WENNFEHLER(TEXT(TEIL(I13;FINDEN("-";I13)+2;8);"TT.MM.JJJJ")*1;J13)))} J14 {=WENNFEHLER(LINKS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8)*1;"")} K14 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8))*1;WENNFEHLER(TEXT(TEIL(I14;FINDEN("-";I14)+2;8);"TT.MM.JJJJ")*1;J14))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I14;MAX(ISTZAHL(TEIL(I14;SPALTE(14:14);1)*1)*SPALTE(14:14)));VERGLEICH(1;ISTZAHL(TEIL(I14&0;SPALTE(14:14);1)*1)*1;0);LÄNGE(I14));8))*1;WENNFEHLER(TEXT(TEIL(I14;FINDEN("-";I14)+2;8);"TT.MM.JJJJ")*1;J14)))} J15 {=WENNFEHLER(LINKS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8)*1;"")} K15 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8))*1;WENNFEHLER(TEXT(TEIL(I15;FINDEN("-";I15)+2;8);"TT.MM.JJJJ")*1;J15))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I15;MAX(ISTZAHL(TEIL(I15;SPALTE(15:15);1)*1)*SPALTE(15:15)));VERGLEICH(1;ISTZAHL(TEIL(I15&0;SPALTE(15:15);1)*1)*1;0);LÄNGE(I15));8))*1;WENNFEHLER(TEXT(TEIL(I15;FINDEN("-";I15)+2;8);"TT.MM.JJJJ")*1;J15)))} J16 {=WENNFEHLER(LINKS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8)*1;"")} K16 {=WENN(WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8))*1;WENNFEHLER(TEXT(TEIL(I16;FINDEN("-";I16)+2;8);"TT.MM.JJJJ")*1;J16))<36500;"";WENNFEHLER(GLÄTTEN(RECHTS(TEIL(LINKS(I16;MAX(ISTZAHL(TEIL(I16;SPALTE(16:16);1)*1)*SPALTE(16:16)));VERGLEICH(1;ISTZAHL(TEIL(I16&0;SPALTE(16:16);1)*1)*1;0);LÄNGE(I16));8))*1;WENNFEHLER(TEXT(TEIL(I16;FINDEN("-";I16)+2;8);"TT.MM.JJJJ")*1;J16)))}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format I9 1. / Formel ist =$K9<HEUTE() Abc I10 1. / Formel ist =$K9<HEUTE() Abc I11 1. / Formel ist =$K9<HEUTE() Abc I12 1. / Formel ist =$K9<HEUTE() Abc I13 1. / Formel ist =$K9<HEUTE() Abc I14 1. / Formel ist =$K9<HEUTE() Abc I15 1. / Formel ist =$K15<HEUTE() Abc I16 1. / Formel ist =$K9<HEUTE() Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

... und wie üblich mein Rat, die Hilfsspalten einfach ausblenden.

[WillWissen]

Hallo Atilla,

schöne VBA-Lösung. Und macht nebenbei noch keinen Unterschied zwischen 2-stelliger oder 4-stelliger Jahreszahl.

[atilla]

Hallo zusammen,

Danke Günter für Dein Feedback. In VBA gibt es die Split Funktion, die es in der Tabelle nicht gibt. Und in diesem Fall kann man sie sehr gut einsetzen, was die Sache sehr vereinfacht.

Mir ist aber gerade aufgefallen, dass das Ereignis Makro nicht immer alle Zellen neu bearbeiten muss.
Es muss nur die gerade geänderte Zelle prüfen, deshalb ist folgender Code besser geeignet:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim i As Long
   Dim arr
   If Target.Count = 1 Then
      If Target.Column = 9 And Target.Row > 8 Then
         On Error GoTo fehler
         Application.EnableEvents = False
         arr = Split(Target)
         If UBound(arr) >= 0 Then
            For i = UBound(arr) To LBound(arr) Step -1
               If IsDate(arr(i)) Then
                  If CDate(arr(i)) < Date Then
                     Target.Interior.ColorIndex = 3
                  Else
                     Target.Interior.ColorIndex = xlNone
                  End If
                  Exit For
               End If
            Next i
         End If
      End If
   Else
      If Target.Column = 9 And Target.Row > 8 Then
         On Error GoTo fehler
         Application.EnableEvents = False
         Datum_kleiner_groesser
      End If
   End If
fehler:
      Application.EnableEvents = True
      If Err Then MsgBox "FEehler: " & Err.Number & vbLf & vbLf & Err.Description
End Sub

[BoskoBiati]

Hallo,

ich setze da immer noch auf Formeln:

Arbeitsblatt mit dem Namen 'Tabelle1'
	I	J	K	L
9	möchte ab 01.01.15 in Filiale A arbeiten	01.01.15		WAHR
10				#WERT!
11				#WERT!
12	Urlaub von 01.12.2014 - 10.12.2014 danach 3.Abteilung	01.12.14	10.12.14	WAHR
13				#WERT!
14				#WERT!
15	Arbeitet von 10.01.15 - 31.01.2015 in Fil. A	10.01.15	31.01.15	FALSCH
16	Urlaub 01.07.14 -14.07.14	01.07.14	14.07.14	WAHR
17				#WERT!
18				#WERT!
19	Urlaub am 10.02.15	10.02.15		FALSCH

Zelle	Formel
J9	=WENNFEHLER(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1;"")
K9	=WENN(ISTZAHL(SUCHEN("-";I9));GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1;"")
L9	=(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1<HEUTE())+WENNFEHLER(((ISTZAHL(SUCHEN("-";I9))*(GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1))<HEUTE())*1;1)>1

Zelle	bedingte Formatierung...	Format
I9	1: =(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1<HEUTE())+WENNFEHLER(((ISTZAHL(SUCHEN("-";I9))*(GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1))<HEUTE())*1;1)>1	abc

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Die Formeln in der Tabelle sind für die bedingte Formatierung nicht notwendig, sie dienen nur der Veranschaulichung!
Leider werden die Farben in I9;I12 u. I16 nicht dargestellt (da, wo die Formel in L ein WAHR ergibt).

[atilla]

Hallo,

ich setze da immer noch auf Formeln:

Hallo Edgar,

da hast Du jetzt aber was primitives rausgehauen

Mit dieser Formel würde ich dann auch überlegen, ob ich VBA nutze.
Denn die bisherigen drückten doch stark auf die Performance Bremse.

Meine VBA Lösung kann aber auch ein allein stehendes Daum verarbeiten, d.h. in der Zelle kann auch nur Datum stehen. :49:

[BoskoBiati]

Hallo,

das können die Formeln auch:

Code:

=ISTZAHL(I9)+(WENNFEHLER(TEIL(I9;SUCHEN("??.??.??";I9);8+ISTZAHL(TEIL(I9;SUCHEN("??.??.????";I9);10)*1)*2)*1;99999)<HEUTE())+WENNFEHLER(((ISTZAHL(SUCHEN("-";I9))*(GLÄTTEN(TEIL(I9;SUCHEN("-";I9)+1;12))*1))<HEUTE())*1;1)>1

[schadre]

Hallo an alle fleißigen Helfer.

Konnte eure Hilfe leider erst heute ausprobieren und habe die Formeln von WillWissen ausprobiert und es klappt. Vielen vielen Dank. Ihr seid die Besten.