Office: (Office 365) Zeile einfärben nach mehreren Kriterien

Hallo, die Spezialisten,

ich habe festgestellt, dass ich mit meinem Problem um das VBA nicht herumkomme. Bitte um kurze Hilfe!

Problembeschreibung:

1. ich habe alle meine Geldausgaben per Online-Banking ins Excel übertragen. In der Spalte "Begünstigter" stehen alle Zahlungsempfänger: Penny, ADAC, Apotheken, DB, AGIP, PayPal, Mcdonalds, etc.
ACHTUNG: außer dieser Begriffe stehen in den selben Zeilen natürlich weitere Details. Beispiel: "ECHO TANKSTELLEN GMBH TUR//Dietmannsried/DE"

2. ich habe mir überlegt, in welchen Kategorien alle Ausgaben unterteilt werden können. Zu jeder Kategorie habe ich die Stichworte in den separaten Spalten notiert.
Beispiel:
Essen (Penny, Ecenter, Lidl, ....)
Auto (ADAC, ARAL, AGIP, ...)
Internet (Telefonica, Unitymedia, Congstar, ...)
...

3. Nun will ich, dass die Spalte "Begünstigter" je nach Suchbegriff aus oberer Liste automatisch eine Farge bekommt.
Beispiel: alle Zeilen wo der Begriff "Penny", "Ecenter", etc. werden grün, alle aus der Kategorie "Auto" (entweder ADAC, oder ARAl oder AGIP ...) werden blau, etc.
Wenn zu jeder Farbe auch noch die entsprechende Kategorie rechts von der Tabelle ausgegeben würde, wäre ich noch glücklicher :-)

4. Wenn mir neue Stichworte zu den jeweiligen Kategorien einfallen (z.B. ich nehme den KAUFLAND in die Kategorie Essen auf), färbt das Macro mir automatisch auch diese neuen Positionen ein.

Danke im Voraus für Eure freundliche Hilfe.
Gruß aus BW!

 

Hola,

geht zwar auch ohne Makro, aber wenn du dir sicher bist.....
Lade eine Exceldatei hoch.

Gruß,
steve1da

 

Moin!
Lade mal eine Beispieltabelle hoch (kein Screenshot!).
Dann muss man das nicht nachbauen.

Gruß Ralf

 

Hali Hallo!

Danke für die Rückmeldungen. Hier ist die abgespeckte Datei. Oben sind die Kategorien und die Stichworte für die Suche.

Grüße!

 

Hi, Steve,
ich habe probiert mit "bedingter Formatierung", wo ich jedes einzelne Stichwort eingetippt habe. Wenn es aber über 30 Stichworte sind, wird es irgendwann langsam nervig. :-)

 

Hallo,

in der beiliegenden Excel-Datei habe ich deine Vorgabe an mehreren Stellen geändert:

1) Im Bereich B17:F17 habe ich Verkettungsformeln der darüber befindlichen Spaltenwerte eingefügt

2) In der Zelle B18 habe ich diese 5 Zellen verkettet, um so einen Mustervergleich-Ausdruck für einen sogenannten "Regulären Mustervergleich" zu erzeugen.

3) Den eigentlichen Tabellenbereich habe ich in eine strukturierte Tabelle umgewandelt (namens "TabBuchungen") und ein neues Feld "Kategorie" (Spalte H) angefügt. In dieser stehen dann die eigentlichen Formeln:
=WENNFEHLER(INDEX($B$1:$F$1;1; SucheInListen([@[Begünstigter/Zahlungspflichtiger]]; $B$18));"")
In dieser befindet sich die benutzerdefinierte Funktion "SucheInListen(Durchsuchstring;Suchmusterstring)": Sie durchsucht mittels des Suchmusterstrings den Durchsuchstring (=Spalte D: Begünstigter/Zahlungspflichtiger). Wird sie fündig, gibt sie eine Ganzzahl zurück, die als Spaltennummer innerhalb des Zellbereichs B1:F1 gedacht ist. War die Suche nicht erfolgreich, gibt die Formel -1 zurück, was in der INDEX-Funktion zu einem Fehler führt.
Die vorige Formel vereinfacht sich dadurch quasi zu =WENNFEHLER(INDEX($B$1:$F$1;1; FormelSpaltenNr);"")

4) Im Punkt 2) wird der Mustervergleich-Ausdruck erzeugt. Dieser funktioniert einwandfrei, wenn im Bereich B2:F15 Strings enthalten sind, die sich aus Buchstaben (groß/klein) und Ziffern zusammensetzen, ferner dürfen auch ein "+", "-" bzw. ein "." enthalten sein. Es dürfen noch weitere Sonderzeichen enthalten sein, doch mit erhöhter Vorsicht - überprüfen, ob die Suche dann noch funktioniert. Auf keinen Fall dürfen es "(" bzw. ")". Falls diese trotzdem enthalten sein müssen, dann nur als "\(" bzw. "\)". Das Backslash-Zeichen dient hier dazu, dass das folgende Zeichen als normales Zeichen und nicht als Zeichen mit einer Spezialfunktion beim Matching interpretiert wird.
Nachzulesen in Kurzfassung in 'Reguläre Ausdrücke - Das VBA-Tutorial

 

Hallo,

ich habe dir die beiden Spalten "Begünstigter/Zahlungspflichtiger" und "Kategorie" der strukt.Tabelle "TabBuchungen" mittels bedingter Formatierung eingefärbt.

Die Farben sind in den Zellen des Kategorienbereiches B1:F1 definiert.
Sie können durch Klicken auf den Button "Kategorienfarben nach Tabelle übertragen" in die vorgenannten bedingten Formate übernommen werden.

 

!!!!! GENIAL !!!! Ich habe das Gefühl, ich fliege zur Betelgeuse :-)
Die Tabelle sieht genauso aus, wie ich sie mir vorgestellt habe. Vielen Dank für deine Mühe und die Kreativität.

Ich muss jetzt den Lösungsweg begreifen. Werde Paar Tage brauchen :-)

Update 10 min later: mein Excel scheint an dieser Funktion zu stolpern: _xlfn.TEXTJOIN("|";WAHR;B2:B15). Gibt "#NAME?" aus. Ich vermute die DE-Version ist die Ursache. Ich schaue, wir ich das vom ENG ins DE umschreiben kann.

Trotzdem: unglaublich hilfreich von dir! Danke!

 

Hallo,

du hast bei der Thread-Eröffnung angegeben, dass du Office365 hast. Aber offenbar besitzt du eine Version, die die Funktion "TEXTVERKETTEN" (englisch: TEXTJOIN) nicht unterstützt, wie hier bei Microsoft nachzulesen ist:
Problem: Vor einer Formel wird das Präfix _xlfn. angezeigt - Office-Support (microsoft.com)

In der nachfolgenden Excel-Datei habe ich dir diese Funktion nachgebaut und habe sie "VERKETTETERTEXT" genannt. Für die Belange in meiner Excel-Datei ist sie ausreichend, obwohl sie nicht ganz identisch zu den Möglichkeiten der Originalfunktion ist.