Hi Forum,
Ich möchte mit der bedingten Formatierung ohne VBA Zellen einfärben, die bestimmte Wörter enthalten. Ich weiß, dass Excel nur drei Bedingungen kennt. Ist es aber möglich, pro Bedingung nach mehreren Wörtern zu suchen. Beispiel
=SUCHEN(„AMAZON*“;C10:smiley:1006)
Mit dieser Formel sollen alle Zellen die „AMAZON“ beinhalten z.B. rot eingefärbt werden. Wie müßte die Formle lauten, um alle Zellen die „AMAZON“ oder „GOOGLE“ enthalten rot einzufärben?
Verwendet: Excel 2003
Mit bestem Dank für die Mühe, Michael
Hi,
wenn das Wort nur in der Zelle steht:
=SUCHEN(A1;„Amazongoogle“;1)
stehen mehrer Wörter in der Zelle
=ODER(ISTZAHL(SUCHEN(„Amazon“;A1;1));ISTZAHL(SUCHEN(„Google“;A1;1)))
Wenn noch mehr Wörter gesucht werden lohnt es sich ne Liste anzufertigen und der nen Namen zu verpassen!
VG REné
Hallo Rene,
1000 Dank, das haut prima hin. Ich wäre aber noch neugierig, wie genau das mit der Liste für noch mehr Wörtern gehen würde.
Mit bestem Dank für deine Geduld, Michael
1000 Dank, das haut prima hin. Ich wäre aber noch neugierig,
wie genau das mit der Liste für noch mehr Wörtern gehen würde.
Hallo Michael,
das könnte so sein:
=Zählenwenn(A1;Liste)>0
oder
=Zählenwenn(Liste;A1)>0
Sorry, ungetestet verwechsel ich da permanent die Reihenfolge.
Gruß
Reinhard
Hallo Reinhard,
mein Gott, wenn man einmal mit Excel anfängt! Ich habe entsprechend deinem Vorschlag in einem neuem Arbeitsblatt einer anderen Mappe eine Liste mit Schlüsselwörtern angelegt. Leider bekommen ich bei dem Versuch, in der Bedingten Formatierung diese Liste durch einfaches anclicken der Liste einzufügen:
=Zählenwenn(A1;[Importmakro.xls]Listen!$A$1)>0
den Fehler: Bezüge auf andere Tabellen oder Arbeitsmappen dürfen in dem Kriterium Bedingte Formatierung nicht verwendet werden. Auch wenn die Liste in der selben Mappe liegt
=Zählenwenn(A1;Listen!$A$1)>0 bekomme ich diesen Fehler.
Sinnvollerweise müßte ich diese Listen aber sowieso in einer anderen Mappe führen. Läßt sich das irgendwie einrichten?
Gruß Michael
Hallo Michael,
mein Gott, wenn man einmal mit Excel anfängt! Ich habe
entsprechend deinem Vorschlag in einem neuem Arbeitsblatt
einer anderen Mappe eine Liste mit Schlüsselwörtern angelegt.
das ist ja prinzipiell korrekt. Mit „Liste“ ist aber gemeint daß du für diese Liste einen Namen vergibst.
Angenommen diese Liste, also eine einpsltige Tabelle, ist in Tabelle2, dann
in Excel ab 2007: Formeln—Namen definieren…
früheres Excel: Einfügen–namen…
einen Namen, z.B. Liste" vergeben für
=Tabelle2!A1:A10
oder
=Tabelle2!A:A
Dann kannst du in Tabelle1 einen Zellbereich markieren, dann auf bed. Formatierung gehen und
=ZÄHLENWENN(Liste;A1)
eingeben. (A1 ist dabei die oberste zelle des markierten Bereiches.
Sinnvollerweise müßte ich diese Listen aber sowieso in einer
anderen Mappe führen. Läßt sich das irgendwie einrichten?
Ja. Das klappt dann wenn die andere Mappe geöffnet ist.
Um das sicherzustellen und diese andere mappe nicht immer manuell starten zu müssen bietet sich dafür die personl.xls an bzw. ein Add-In (xla)
Dann würde die Namens-Formel so aussehen:
=[Bestell3.xls]FiBu!A1:A10
Gruß
Reinhard
Hallo Reinhard,
gibt´s eigentlich auch ein Excel ohne Formeln? Okay, ganz im Ernst: Per Namen greift die bedingte Formatierung jetzt auf die Listen zu, das ist ein Fortschritt. Aber !! was immer ich auch anstelle, mit dieser Formel wird immer nur der erste Listeneintrag erfasst:
=ZÄHLENWENN(A1;Test)>0
Die Liste Test umfasst vier Zeilen
=Tabelle2!$A$1:blush:A$4
z.B. *Google*, *Amazon*, *Ebay*, *Paypal*. Steht Google in der ersten Zeile, werden alle Zellen mit Google eingefärbt - die anderen Zellen mit Einträgen der Liste aber nicht. Tausche ich und bringe Ebay in die erste Zeile, dann werden die Ebay Zellen eingefärbt, Google Zellen bleiben weiß.
Ich bin zuversichtlich, du hast auch dafür einen Rat.
Mit bestem Dank im Voraus, Michael
Grüezi Michael
;Aber !! was immer ich
auch anstelle, mit dieser Formel wird immer nur der erste
Listeneintrag erfasst:
=ZÄHLENWENN(A1;Test)>0
Ich glaube dir ist hier passiert was Reinhard für sich beansprucht hat - Du hast die Parameter in der Formel vertauscht… 
Die Liste Test umfasst vier Zeilen
=Tabelle2!$A$1:blush:A$4
z.B. *Google*, *Amazon*, *Ebay*, *Paypal*. Steht Google in der
ersten Zeile, werden alle Zellen mit Google eingefärbt - die
anderen Zellen mit Einträgen der Liste aber nicht. Tausche ich
und bringe Ebay in die erste Zeile, dann werden die Ebay
Zellen eingefärbt, Google Zellen bleiben weiß.
Du suchst hier den Begriff, der in ‚Test‘ steht (und da wird dann die erste Zelle genommen) in A1.
Die korrekte Syntax lautet (sowas steht auch jeweils in der Hilfe zur Funktion):
ZÄHLENWENN(Bereich; Suchkriterien)
Für dich daher:
=ZÄHLENWENN(Test;A1)>0
Mit freundlichen Grüssen
Thomas Ramel
Hallo und einen Gruß an den Schweizer Frühaufsteher,
ich hatte vergessen zu schreiben, dass ich vor meiner Nachfrage schon beide Varianten ausprobiert hatte, also sowohl
=Zählenwenn(A1;Test)>0
als auch
=Zählenwenn(Test;A1)>0
Dabei findet
=ZÄHLENWENN(A1;Test)>0
wie beschrieben jeweils nur den ersten Listeneintrag, wohingegen
=ZÄHLENWENN(Test;A1)>0 überhaupt keine Treffer bringt.
Wie schon gesagt: Name „Test“ ist definiert für das Tabellenblatt Listen,also
=Listen!$A$1:blush:A$4
und A1 bis A4 enthalten der Reihe nach
*Google*
*Amazon*
*paypal*
*Live*
Was kann denn hier noch falsch laufen ?
Gruß Michael
Grüezi Michael
wohingegen
=ZÄHLENWENN(Test;A1)>0 überhaupt keine Treffer bringt.
Wie schon gesagt: Name „Test“ ist definiert für das
Tabellenblatt Listen,also
=Listen!$A$1:blush:A$4
und A1 bis A4 enthalten der Reihe nach
*Google*
*Amazon*
*paypal*
*Live*
Was kann denn hier noch falsch laufen ?
Hmmm, was genau steht denn in A1 in dem Tabellenblatt auf dem du die Bedingte Formatierung anwenden willst?
Oder wo genau ist die Zelle in der dein Begriff enthalten ist?
Die Addresse in der Formel solltest Du natürlich auf deinen Bereicht (deine Zelle) anpasse, das liegt IMO auf der Hand.
Damit =ZÄHLENWENN(Test;A1)>0 vergleichst Du den Inhalt der Zelle A1 mit deiner Liste…
Mit freundlichen Grüssen
Thomas Ramel
Dabei findet
=ZÄHLENWENN(A1;Test)>0
wie beschrieben jeweils nur den ersten Listeneintrag,
wohingegen
=ZÄHLENWENN(Test;A1)>0 überhaupt keine Treffer bringt.
Wie schon gesagt: Name „Test“ ist definiert für das
Tabellenblatt Listen,also
=Listen!$A$1:blush:A$4
und A1 bis A4 enthalten der Reihe nach
*Google*
*Amazon*
*paypal*
*Live*
Hallo Michael,
ich kann das so nicht nachstellen um den Fehler zu erreichen, schau mal hier, da sind in den beiden gerahmten Bereichen deine beiden Formelvarianten wobei natürlich die obere die richtige ist.
Gruß
Reinhard
Hallo Ihr lieben Helfer Reinhard und Thomas,
mittels der Beispieltabelle (die natürlich funktioniert) habe ich den Fehler gefunden: Die Listeneinträge erlauben für die Formel
=ZÄHLENWENN(Test;A1)>0
keine Wildcards - die ich aber unbedingt benötige, weil die Schlüsselwörter oft nicht allein in der Zelle stehen (darum hatte ich auch meine Beispiele immer in *xxx* gesetzt).
Erstaunlicherweise sind bei der Umkehrung
=ZÄHLENWENN(A1;Test)>0 aber Wildcards möglich.
Problem also scheinbar erkannt - aber leider noch nicht gelöst: Gibt es die segensreiche Verbindung Liste mit Wildcards - und wie sieht sie aus?
Gruß Michael
mittels der Beispieltabelle (die natürlich funktioniert) habe
ich den Fehler gefunden: Die Listeneinträge erlauben für die
Formel
=ZÄHLENWENN(Test;A1)>0
keine Wildcards - die ich aber unbedingt benötige, weil die
Schlüsselwörter oft nicht allein in der Zelle stehen
Hallo michael,
um das Ganze zu beschleunigen und abzukürzen, lade mit
http://www.file-upload.net
mal meine Beispielmappe hoch in der du Wildcards benutzt.
Gruß
Reinhard
Hallo Reinhard,
ich nehme dein Angebot gern an.
http://www.file-upload.net/download-3639251/kwverkn-…
Ich habe auf dem Blatt Listen, Spalte C die Schlüsselwörter jeweils mit ** eingegeben, dafür den Namen Wildcardtest definiert und dann die Beispieltabellen kopiert und in den Kopien jeweils die Bedingte Formatierung auf
=ZÄHLENWENN(Wildcardtest;A1)>0 bzw.
=ZÄHLENWENN(A1;Wildcardtest)>0 geändert.
Daraufhin werden die Schlüsselwörter wie von mir beschrieben eingefärbt oder eben auch nicht.
Vielen Dank für die Geduld, Michael
Grüezi Michael
Auch für dich gilt hier:
Dann schildere doch mal detailliert, was denn die Anforderungen sind.
Welche Inhalte habe deine Zellen im ersten Tabellenblatt - womit ist da zu rechnen?
Und wonach genau willst Du denn nun suchen - offenbar ob einer der Begriffe im zweiten Tabellenblatt Teil des Inhalts einer Zelle auf dem ersten Tabellenblatt ist?
Das geht nämlich auch aus deiner Mappe nicht wirklich hervor, da hat es von allem etwas drin, aber nichts wirklich Konkretes.
mittels der Beispieltabelle (die natürlich funktioniert) habe
ich den Fehler gefunden: Die Listeneinträge erlauben für die
Formel
=ZÄHLENWENN(Test;A1)>0
keine Wildcards - die ich aber unbedingt benötige, weil die
Schlüsselwörter oft nicht allein in der Zelle stehen (darum
hatte ich auch meine Beispiele immer in *xxx* gesetzt).
Und warum um aller Welten Willen sagst Du uns das nicht?
Woher wollen wir wissen, dass Du nicht einfach den Begriff in deinem Beitrag hervorheben wolltest?
Merke:
Jede Antwort kann nur so detailliert und fundiert sein wie es auch deine Fragestellung ist (mal von ein paar Sternstunden und der Kristallkugel abgesehen…).
Erstaunlicherweise sind bei der Umkehrung
=ZÄHLENWENN(A1;Test)>0 aber Wildcards möglich.
Bitte lies erstmal die Online-Hilfe zur Funktion ZÄHLENWENN() aufmerksam und so lange durch bis Du verstanden hast wie die Sache funktioniert und welcher Parameter wofür dient - und dann vergiss bitte allerschnellstens diese ‚Umkehr-Versuche‘ mit der Formel!
Nochmals zusammengefasst:
Du hast in einem Tabellenblatt Inhalte in Zellen die (auch) aus mehreren Begriffen bestehen können.
Du willst nun diese Zellen formatieren die (unter anderem und nicht ausschliesslich) ein Wort aus einer Liste enthält, die auf einem zweiten Tabellenblatt erstellt worden ist.
Richtig so?
Mit freundlichen Grüssen
Thomas Ramel
Hallo Thomas,
>>>Du hast in einem Tabellenblatt Inhalte in Zellen die (auch) aus
mehreren Begriffen bestehen können.
Du willst nun diese Zellen formatieren die (unter anderem und
nicht ausschliesslich) ein Wort aus einer Liste enthält, die auf
einem zweiten Tabellenblatt erstellt worden ist.
Richtig so?
ja, genauso!
Gruß Michael
Grüezi Michael
>>>Du hast in einem Tabellenblatt Inhalte in Zellen die (auch)
aus
mehreren Begriffen bestehen können.
Du willst nun diese Zellen formatieren die (unter anderem und
nicht ausschliesslich) ein Wort aus einer Liste enthält, die
auf einem zweiten Tabellenblatt erstellt worden ist.
Richtig so?
ja, genauso!
Endlich *seufz*
Das kannst Du so mit normalen und/oder einigermassen einfachen Formeln nicht ‚erschlagen‘, da hier in die falsche Richtung gesucht wird (darum war, als Du die Parameter vertauscht hast, ein Teilerfolg zu verbuchen).
Füge die folgenden VBA-Zeilen in ein allgemeines Modul der Mappe ein:
-
Rechtsklick auf einen Tabellenblattreiter
-
Code anzeigen
-
Im VBA-Editor Menü:‚Einfügen‘
-
Modul
-
In dieses leere Fenster die Code-Zeilen 1:1 kopieren
-
Mit ALT+Q den VBA-Editor verlassen (vielleicht
Nun kannst Du deinen Bereich im ersten Tabellenblatt markieren und die folgende Formel verwenden um die Bedingte Formatierung zu aktivieren (den Bezug auf die aktive Zelle anpassen):
=tr_SearchWord(E12;Test)
Wenn Du noch einen 3.Parameter setzt, kannst Du das Ganze auch mit Berücksichtigung der Gross/Klein Schreibweise tun:
=tr_SearchWord(E12;Test;WAHR)
Und hier noch die Code-Zeilen:
Public Function tr\_SearchWord(rngZelle As Range, \_
rngListe As Range, \_
Optional GrossKlein As Boolean = False) As Boolean
Dim rngWord As Range
If GrossKlein = True Then
For Each rngWord In rngListe.Cells
If InStr(1, rngZelle, rngWord) \> 0 Then
tr\_SearchWord = True
Exit Function
End If
Next rngWord
Else
For Each rngWord In rngListe.Cells
If InStr(1, UCase(rngZelle), UCase(rngWord)) \> 0 Then
tr\_SearchWord = True
Exit Function
End If
Next rngWord
End If
tr\_SearchWord = False
End Function
Mit freundlichen Grüssen
Thomas Ramel
noch speichern)
http://www.file-upload.net/download-3639251/kwverkn-…
Hallo Michael,
wie wäre es denn mit Vba?
Alt+F11, Einfügen—Modul, Code reinkopieren, Editor schließen.
Bei bedingter Formatierung dann Formel ist
=Finde(E1)
wobei dann in Tabelle2 die Suchwörter untereinander in A1:Ax stehen sollen ohne Sternchen.
Gruß
Reinhard
Option Explicit
Function Finde(ByRef Zelle As Range) As Boolean
Dim Zei As Long
With Worksheets("Listen")
For Zei = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
If Zelle.Value "" And InStr(UCase(Zelle.Value), UCase(.Cells(Zei, 1).Value)) \> 0 Then
Finde = True
Exit Function
End If
Next Zei
End With
End Function
Hallo Reinhard,
da VBA mein Stiefkind ist…eher ein Kind, was ich zur Adoption frei gab…hier was mit Formeln:
=VERWEIS(9^9;SUCHEN(Test;E12))
=VERWEIS(9^9;SUCHEN(Test;E12))
Hallo René,
hast du die Formel „geboren“?
Erklär sie mir mal bitte.
Wenn nicht heute dann kommste morgen zu spät, denn da werde ich sie auuuuuuusgiebig testen um auf ihr Geheimnis zu kommen 
)
Kann doch gar nicht sein daß so eine winzige Formel funktioniert und ich weiß nicht warum.
Naja, ich hab glaub gestern SUCHEN(Test;E12) einzeln getestet, kam immer eine Fehlermeldung.
May be ich hab da einen Fehler gemacht.
9^9 sehe ich als große zahl an, sonst nix. Wird aber morgen gleich mitgetestet und erstmal durch 4711 ersetzt.
Wenn du Jungspund überhaupt weißt wofür 4711 steht/stand *lächel*
Schönes Rest-WE für dich
Gruß
Reinhard