Zeilenauswahl nach Farbe

[DieAuster]

Hallo,

Ich bin ganz neu hier und erst einmal sehr begeistert. Ich konnte mich schon durch Beiträge hier etwas weiterbilden und Anfänge meines Problems lösen.
Falls dieses Problem so nicht in das Forum gehört oder ich zu "ungebildet" laut meines Wissenstandes zum Thema Excel bin, bitte dieses Thema einfach löschen.

Es ist eine Aufgabe die ich bei einem Nebenjob als Projekt erhalten habe. Durch kleine Exkurse in die Informatik in meinem Studium habe ich interesse dieses zu lösen und schon Programmablaufpläne erstellt. Nur das Know-How fehlt mir zur Lösung an manchen Stellen:


Ich möchte kurz mein allgemein zu Lösendes Problem erläutern:

Ich habe eine Tabelle in der ersten Seite/Mappe der Excel Datei, diese wird wöchentlich mit neuen Infos gefüllt. Der Inhalt dieser Tabelle sind Grundlegende Werte für einen Serienbrief. Also Name, Adresse, PLZ, Ort etc. und dazu noch ein Termindatum.

Nun möchte ich einen Serienbrief erstellen, wenn das Datum des Kunden eine bestimmte Bedingung zum heutigen Datum hat. Als Beispiel 6 Wochen vorher. Ein zweiter Serienbrief, wenn das Termindatum nur noch 1ne Woche zum heutigen Datum hin ist.

Laut meiner "Nachforschung" und um es für mich Anfänger einfach zu halten, würde ich nun wollen, dass zum einen über eine Tabellenformatierung die Zeile "gelb" wird, wenn das heutige Datum und das eingegebene Datum 6 Wochen oder weniger auseinadner liegen in Mappe 1. Und dann alle gelben Zeilen über ein Makro oder ähnliches in eine zweite Tabelle auf Seite2/Mappe2 kopieren, sodass aus dieser der serienbrief erstellt werden kann.

Das gleiche dann für die roten Zeilen in eine Mappe3/Seite3.


Ich kann bei Bedarf einfach mal eine Beispiel Tabelle erstellen:

Meine Fragen sind also:

1)Wie kann ich eine eigenen Bedingung unter bedingte Formatierung der Tabelle so formulieren oder erstellen, dass er die Zeilen in der Wunschfarbe einfärbt, sobald ein Datum erreicht wird?

2)Wie extrahiere ich anhand einer Zeilenfarbe die Zeilen und füge sie einer neuen Tabelle auf Seite2/Mappe2 hinzu?

Zusatz:

Dazu kommen noch Kleinigkeiten, die ich später nachfragen würde....

Ich bedanke mich für jeden Antwort!

[Käpt'n Blaubär]

Hallo,

Ich bin ganz neu hier und erst einmal sehr begeistert. Ich konnte mich schon durch Beiträge hier etwas weiterbilden und Anfänge meines Problems lösen.

Falls dieses Problem so nicht in das Forum gehört oder ich zu "ungebildet" laut meines Wissenstandes zum Thema Excel bin, bitte dieses Thema einfach löschen.

gemach, ... gemach

es gibt keine dummen Fragen, höchstens dumme Antworten

[WillWissen]

Hi,

zu deiner ersten Frage: das geht mittels bedingter Formatierung:

Tabelle5

	B
1	28.02.2017
2	01.03.2017

Bedingte Formatierungen der Tabelle

Zelle Nr.: / Bedingung Format B1 1. / Formel ist =HEUTE()<=$B1 Abc B2 1. / Formel ist =HEUTE()<=$B1 Abc

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Dein zweiter Wunsch dürfte mMn nur mit VBA zu lösen sein. Da gibt es einige Spezialisten, die dir helfen können.

[Basisa]

Hi, hier ein Lösungsvorschlag, habe allerdings bisschen mehr hinein gepackt... ( DATEI IM ANHANG )

Bedingte Formatierung:

Code:

=UND(HEUTE()-A2<=0;HEUTE()-A2>-42)

VBA:

Code:

Option Explicit

Sub Test()
Dim i, MaxZeile, MaxZeile2 As Integer
Dim Nachricht As String

' Maximale Zeile?
MaxZeile = Range("A65000").End(xlUp).Row
' Nachricht löschen
Nachricht = ""

' Prüft welche Zeile zutrifft
For i = 2 To MaxZeile
   If Date - CDate(Cells(i, 1).Value) <= 0 And Date - CDate(Cells(i, 1).Value) > -42 Then
       Nachricht = Nachricht & Cells(i, 1).Address & Chr(13)
   End If
Next

' Gibt die Nachricht aus!
If MsgBox("Folgende Zellen treffen zu:" & Chr(13) & _
      "----------------------------" & Chr(13) & Chr(13) & _
      Nachricht & Chr(13) & Chr(13) & _
      "Sollen diese auf Tabelle2 geschrieben werden?", vbYesNo + vbInformation, "Was soll ich machen?") = vbNo Then Exit Sub

' Löscht erst einmal wieder alles auf Tabelle2
MaxZeile2 = Worksheets("Tabelle2").Range("A65000").End(xlUp).Row
Worksheets("Tabelle2").Range(Sheets("Tabelle2").Cells(1, 1), Sheets("Tabelle2").Cells(MaxZeile2, 1)).ClearContents
MaxZeile2 = 1 ' Fängt hier an zu schreiben

' Kopiert die Zeilen nach Tabelle2
For i = 2 To Range("A65000").End(xlUp).Row
   If Date - CDate(Cells(i, 1).Value) <= 0 And Date - CDate(Cells(i, 1).Value) > -42 Then
       Sheets("Tabelle2").Cells(MaxZeile2, 1).Value = Cells(i, 1).Value
       MaxZeile2 = MaxZeile2 + 1
   End If
Next

End Sub

' #################################################################
' Das ganze geht auch kürzer, ich wollte nur mal zeigen
' was man so machen könnte, damit es schönes aussieht etc.
' Kannst dich gerne über VBA belesen!
' Gruß Basisa
' #################################################################

[DieAuster]

Vielen Dank erst einmal für die netten und schnellen Antworten 

Nun habe ich eine Frage zu dem Code:

Bedingte Formatierung:
Code:

Code:

=UND(HEUTE()-A2<=0;HEUTE()-A2>-42)

Dieser Code gibt die Bedingung an, dass das Datum vor dem heutigen Tag liegt und höchstens 42 Tage vorher. Wieso steht dort nur A2, ist das eine Formel, bzw. bedingte Formatierung, welche ich für jede einzelne Zeile ausführen muss. Weil bei einer sich immer ändernden Tabelle, weiß ich ja nicht wie viele Zeilen es zur Zeit gibt, oder?


Am ende meiner Tabelle wird es eine Spalte mit "ja" oder "nein" geben, da ich dafür sorgen muss, das schon in Tabelle zwei kopierte und gedruckte Kunden mit Datum ( zum Beispiel eine Woche zuvor) nicht mehr neu gelb markiert werden. Ist dies so möglich?

=UND(HEUTE()-A2<=0;HEUTE()-A2>-42;B2="Nein")

[Rabe]

Hi,

Wieso steht dort nur A2, ist das eine Formel, bzw. bedingte Formatierung, welche ich für jede einzelne Zeile ausführen muss. Weil bei einer sich immer ändernden Tabelle, weiß ich ja nicht wie viele Zeilen es zur Zeit gibt, oder?


Am ende meiner Tabelle wird es eine Spalte mit "ja" oder "nein" geben, da ich dafür sorgen muss, das schon in Tabelle zwei kopierte und gedruckte Kunden mit Datum ( zum Beispiel eine Woche zuvor) nicht mehr neu gelb markiert werden. Ist dies so möglich?

=UND(HEUTE()-A2<=0;HEUTE()-A2>-42;B2="Nein")

Du markierst den gesamten Bereich, der bedingt gefärbt werden soll.
Dann gibst Du in der bed. Formatierung genau diese Formel ein.
Dann gilt die Bedingung in der jeweiligen Zeile.

Wenn Du nun in Deinen Datenbereich klickst und STRG-L drückst, kannst Du das in eine Liste umwandeln, die die Formatierung und mögliche Formeln automatisch nach unten weiterführt, wenn Du Daten/Zeilen darunter hinzufügst.

Zur zweiten Frage:
Ja, das müßte so gehen.

[DieAuster]

Hallo,

Danke für die ganzen Antworten. Das mit dem "Aufploppenden" Fenster benötige ich nicht, ich würde dies gerne über eine Form als Button benutzen. Mit einem Makro. Zudem bin ich leider nicht durch die Antwort ( den VBA Stoff ) durchgestiegen. Vielleicht ist das zu naiv für mich, ich würde doch gerne meine Antwort genau berstehen um bei Problemen direkt selber diese beheben zu können.

Dieses Programm hilft mir bei der Ablage in einem Nebenjob, welche ich sonst per Hand machen müsste.

Im Anhang die Datei als Beispiel:

Leider funktioniert das gelbe Markieren auch noch nicht richtig, hatte es aber in dem anderen Beispiel ja.

[Rabe]

Hi,

Im Anhang die Datei als Beispiel:

Leider funktioniert das gelbe Markieren auch noch nicht richtig, hatte es aber in dem anderen Beispiel ja.

hier mal Dein Makro 6 anders programmiert:

Option Explicit



Sub Makro6()

' 

' Makro6 Makro 

' 

Dim loLetzte As Long

' 

loLetzte = Sheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row           ' letzte belegte in Spalte A (1) 

   Sheets("Tabelle2").Range("B3:B6").Copy

   Sheets("Tabelle1").Range("A" & loLetzte + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

       :=False, Transpose:=True

   Sheets("Tabelle2").Range("B3:B6").ClearContents



End Sub

Deine bedingte Formatierung funktioniert nicht, weil Du die Spalte A von dem heutigen Datum abziehst und da in A der Name steht, wird das nie wahr. Die Datumsspalte ist die Spalte D und das Nein steht nicht in B sondern in E. Außerdem mußt Du mit $ die Spalten festschreiben.

Tabelle1

	A	B	C	D	E	F
1	Name	Vorname	Straße	Datum	Gelber Brief gedruckt?	Spalte6
2	a	b	c	06.03.2017	Nein
3	Marius	Maus	Baumweg	28.02.2017	Nein

Bedingte Formatierung Haupttabelle 1
Wird angewendet auf	Nr. Bed.  Regeltyp	Operator	Formel1	Formel2	Format Schrift Füllfarbe	Unterstrichen	Schrift- farbe	Muster	Musterfarbe	Typ	Bereich
$A$2:$F$3	01.Bed.: Formel ist		=UND(HEUTE()-$D2<=0;HEUTE()-$D2>-42;$E2="Nein")		65535					2	A2:F3

Bedingte Formatierung Haupttabelle 2					oberere/unterer Bereich
Wird angewendet auf	Nr. Bed.	Format Zelle	Anhalten	Auswahl	Anzeige	Anzahl	Durchschnitt	Typ	Bereich
$A$2:$F$3	01.Bed.		Wahr					2	A2:F3

Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.08 einschl. 64 Bit

  TESTBEISPIEL - Rabe.xlsm (Größe: 22,67 KB / Downloads: 1)

[DieAuster]

Hallo Rabe,

Danke für die hilfreiche Antwort, bis dahin ist mein Problem also gelöst und ich habe deine Änderungen verstanden.

Nun verstehe ich nur noch diesen Teil nicht:

Sub Test()

Dim i, MaxZeile, MaxZeile2 As Integer

' Kopiert die Zeilen nach Tabelle2
For i = 2 To Range("A65000").End(xlUp).Row
   If Date - CDate(Cells(i, 1).Value) <= 0 And Date - CDate(Cells(i, 1).Value) > -42 Then
       Sheets("Tabelle2").Cells(MaxZeile2, 1).Value = Cells(i, 1).Value
       MaxZeile2 = MaxZeile2 + 1
   End If
Next

End Sub

Die Idee mit dem Anzeigefeld, welches aufgeht und die Zeilen angibt, welche dann kopiert werden ist hübsch aber mir etwas zu kompliziert. Ich dachte ich könnte diesen Text kopieren und als Makro speichern, welches auf "Knopfdruck" meine markierten Zeilen kopiert.

Sub Test()

Dim i, MaxZeile, MaxZeile2 As Integer

' Kopiert die Zeilen nach Tabelle2 
For i = 2 To Range("A65000").End(xlUp).Row  '1) gibt das die Anzahl der Zeilen an( hier 65.000 ?)
   If Date - CDate(Cells(i, 1).Value) <= 0 And Date - CDate(Cells(i, 1).Value) > -42 Then  'Ist es hier möglich als Bedingung einach die Zellenarbe zu nehmen, da bei mir noch mehr Bedingungen wie das Datum dazu kommen ( Beispiel: In spalte D="Nein" und Spalte E="Nein" ) etc.
       Sheets("Tabelle2").Cells(MaxZeile2, 1).Value = Cells(i, 1).Value   ' Kopiert dieser Befehl die Zeilen? Von Tabelle2 nach ?? oder von dem aktuellen Blatt zum Blatt Tabelle2  was beduetet das Cells(i, 1) ? weil i die entsrpechende Zeile ist aber das 1 die Spalte?
       MaxZeile2 = MaxZeile2 + 1
   End If
Next

End Sub



Ich bedanke mich schonmal für die Mühe von dem, der mir diese doofen Fragen beantwortet. Leider konnte ich mir dies nicht selber erklären.

Liebe Grüße

Die Auster

[Rabe]

Hi,

Die Idee mit dem Anzeigefeld, welches aufgeht und die Zeilen angibt, welche dann kopiert werden ist hübsch aber mir etwas zu kompliziert. Ich dachte ich könnte diesen Text kopieren und als Makro speichern, welches auf "Knopfdruck" meine markierten Zeilen kopiert.

Die Idee mit der Messagebox war vermutlich, um zu zeigen, wie eine Abfrage, ob wirklich kopiert werden soll, zu realisieren ist. Ich habe sie nun entfernt.
Es ist schlecht, wenn die Tabellen andere Namen als die VBA-Namen haben (also die Namen im VBA-Editor in der Klammer und vor der Klammer), dies habe ich angeglichen.

Ich habe Dir mal das Makro etwas verkürzt, umgestrickt und erläutert:

Sub gelbe_kopieren()

  Dim i As Long

  Dim j As Long

  Dim MaxZeile As Long

  Dim MaxZeile2 As Long

  

  ' letzte belegte Zeile in Tabelle3 

  MaxZeile2 = Worksheets("Tabelle3").Cells(Rows.Count, 1).End(xlUp).Row           ' letzte belegte in Spalte A (1) 

  MaxZeile2 = MaxZeile2 + 1 ' erste leere Zeile; fängt hier an zu schreiben 

  

  ' letzte belegte Zeile in Tabelle1 

  MaxZeile = Worksheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row           ' letzte belegte in Spalte A (1) 

  

  'prüft jede belegte Zeile in Tabelle1 ohne Kopfzeile 

  For i = 2 To MaxZeile

     If Date - CDate(Cells(i, 4).Value) <= 0 And Date - CDate(Cells(i, 4).Value) > -42 Then

        ' Kopiert 6 Spalten der Zeilen nach Tabelle3 

        For j = 1 To 6

           Sheets("Tabelle3").Cells(MaxZeile2, j).Value = Cells(i, j).Value

        Next j

        MaxZeile2 = MaxZeile2 + 1

     End If

  Next

  

End Sub

Bedingt formatierte Farben können vom Excel-Makro nicht ausgelesen werden, deswegen muß die Bedingung über die Formel im Code geprüft werden.

Es wurde auf die falsche Tabelle kopiert und nur die erste Spalte der jeweiligen Zeile.
Dies habe ich über die For-Schleife mit j korrigiert.

Cells(i,j) bedeutet Zeile i und Spalte j (für i = 3 und j = 6):
Cells(3,6) = Cells(3,"F") = Range("F3")


  TESTBEISPIEL - Rabe V2.xlsm (Größe: 24,45 KB / Downloads: 0)

Das Makro für Tabelle3 zum Eintragen von "Ja" in Tabelle1 muß noch erstellt werden! Dazu sollte in beiden Tabellen eine Spalte eingefügt werden mit einer eineindeutigen ID zur Erkennung, in welche Zeile ein "Ja" eingetragen werden soll. Dann muß noch das Löschen der Zeilen in Tabelle3 eingefügt werden.