Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Bedingte Formatierung
#21
Hi schadre,

Zitat: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

Zitat: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

Zitat: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.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#22
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
Gruß Atilla
Antworten Top
#23
Hi,

nachdem dies deine Vorgabe ist

Zitat: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

IJKL
9möchte ab 01.03.15 in Filiale A arbeiten01.03.201501.03.2015zukünftig
10Urlaub von 15.1.15 - 10.02.1515.01.201510.02.2015zweites Datum zukünftig
11Seminar 01.01.15 - 20.01.1501.01.201520.01.2015beide Daten Vergangenheit
12ab 15.01.15 in 3. Abtlg.15.01.201515.01.2015Datum Vergangenheit + 3. Abtlg. hinten
13Urlaub 01.01.15 - 22.1.1501.01.201522.01.2015erstes Datum Vergangenheit, zweites heute
143. Abtl. bis zum Urlaub am 13.12.1413.12.2014Vergangenheit + 3. Abtlg. vorne
153. Abtl. bis zum Urlaub am 1.02.1501.02.2015Datum zukünftig + 3. Abtlg. vorne
16Meier 2. Abmahnung02.01.1900kein Datum
Formeln der Tabelle
ZelleFormel
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
ZelleNr.: / BedingungFormat
I91. / Formel ist =$K9<HEUTE()Abc
I101. / Formel ist =$K9<HEUTE()Abc
I111. / Formel ist =$K9<HEUTE()Abc
I121. / Formel ist =$K9<HEUTE()Abc
I131. / Formel ist =$K9<HEUTE()Abc
I141. / Formel ist =$K9<HEUTE()Abc
I151. / Formel ist =$K15<HEUTE()Abc
I161. / Formel ist =$K9<HEUTE()Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

... und wie üblich mein Rat, die Hilfsspalten einfach ausblenden.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
[-] Folgende(r) 1 Nutzer sagt Danke an WillWissen für diesen Beitrag:
  • schadre
Antworten Top
#24
Hallo Atilla,

schöne VBA-Lösung. Und macht nebenbei noch keinen Unterschied zwischen 2-stelliger oder 4-stelliger Jahreszahl. Thumps_up
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#25
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
Gruß Atilla
[-] Folgende(r) 1 Nutzer sagt Danke an atilla für diesen Beitrag:
  • schadre
Antworten Top
#26
Hallo,

ich setze da immer noch auf Formeln:

Arbeitsblatt mit dem Namen 'Tabelle1'
 IJKL
9möchte ab 01.01.15 in Filiale A arbeiten01.01.15 WAHR
10   #WERT!
11   #WERT!
12Urlaub von 01.12.2014 - 10.12.2014 danach 3.Abteilung01.12.1410.12.14WAHR
13   #WERT!
14   #WERT!
15Arbeitet von 10.01.15 - 31.01.2015 in Fil. A10.01.1531.01.15FALSCH
16Urlaub 01.07.14 -14.07.1401.07.1414.07.14WAHR
17   #WERT!
18   #WERT!
19Urlaub am 10.02.1510.02.15 FALSCH

ZelleFormel
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

Zellebedingte Formatierung...Format
I91: =(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)>1abc
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).
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#27
(22.01.2015, 11:18)BoskoBiati schrieb: Hallo,

ich setze da immer noch auf Formeln:

Hallo Edgar,

da hast Du jetzt aber was primitives rausgehauen Wink

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:
Gruß Atilla
Antworten Top
#28
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
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
Antworten Top
#29
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.
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste