Excel Wörter aufgrund Buchstabenkombination Farbe ändern

asrue · 14. November 2019 um 14:00

Hallo an die Wissenden,

ich habe folgendes Excelproblem und bin bei der googelei nicht so richtig weitergekommen. Ich hoffe hier ein paar Experten zu finden. Das Problem:

Ich habe in einer Exceltabelle teilweise nur ein Wort in einer Zelle stehen und teilweise mehrere Worte, welche durch Zeilenumbruch innerhalb der Zelle getrennt sind. Bei den Wörtern handelt es sich um Namen. Z.B. Müller, Meier, Schmitt, Hunz, Kunz, usw.

Jetzt möchte ich beispielsweise allen Namen die Farbe gelb zurodnen lassen, welche die Buchstabenkombination üll beinhalten (und zwar gleich ob groß oder klein geschrieben). Im obigen Beispiel müsste dann der Name Müller der einzige Name sein, der gelb eingefärbt ist (Schriftfarbe).

Kann mir jemand bei dieser Aufgabenstellung weiterhelfen?

abgemeldet5 · 14. November 2019 um 14:00

Ich habe in einer Exceltabelle teilweise nur ein Wort in einer Zelle stehen und teilweise mehrere Worte, welche durch Zeilenumbruch innerhalb der Zelle getrennt sind.

…allen Namen die Farbe gelb zuordnen lassen, welche die Buchstabenkombination üll beinhalten

Hallo
wenn >Müller, Meierbedingte Formatierung
>Formel zur Ermittlung…
>Werte formatieren … : =SUCHEN(„Üll“;A2)
> wenn „Üll“ in einer Zelle steht, bitte absoluten Bezug verwenden zB. $A$1
> beachte das hier die oberste linke markiere Zelle A2 ist, sonnst bitte anpassen
>Formatierung wählen
>ok

asrue · 14. November 2019 um 14:00

Mehrere Wörtern in der gleichen Zelle war mein Hauptproblem. An die bedingte Formatierung hatte ich auch schon gedacht, aber wieder verworfen.

Trotzdem vielen Dank für die rasche Antwort

abgemeldet26 · 14. November 2019 um 14:01

Hallo asrue,

wenn es ausreichen würde, dass die Schriftfarbe sich ändert und nicht der Hintergrund, dann könnten wir das mit einem VBA-Makro lösen.
Melde dich bei Interesse.

Gruß, Andreas

Niclaus_Wurthberg · 14. November 2019 um 14:02

könnten wir das mit einem VBA-Makro lösen.
Melde dich bei Interesse.

Hallo Andreas

Interesse hätte ich! Würdest Du das Makro auch mir „verraten“?

Freundliche Grüsse Niclaus

abgemeldet26 · 14. November 2019 um 14:02

Hallo Niclaus,

ich dachte an sowas in der Art:

Sub NamenFärben()
 Dim i As Long
 Dim namen As Variant
 Dim suchString As String
 Dim anfang As Long

 suchString = "üll"
 With ActiveCell
 anfang = 1

 ' Wenn die Namen in einer Zelle untereinander stehen, wurden sie mit ALT+Eingabetaste eingegeben.
 ' Dann steht zwischen den Namen immer ein Chr(10)
 ' Im Feld "namen" werden die Namen einzeln abgelegt.
 namen = Split(.Value, Chr(10))

 ' Jetzt werden die Namen nacheinander nach dem Suchstring durchsucht.
 For i = 0 To UBound(namen)
 If InStr(LCase(namen(i)), suchString) \> 0 Then

 ' Wenn ein Name den Suchstring enthält, wird dieser Teil des Zellentextes rot gefärbt.
 ' Der erste Buchstabe, der rot werden muss, wird durch den Wert in "anfang" festgelegt,
 ' die Länge der Rotfärbung ist die Länge des Namens.
 .Characters(Start:=anfang, Length:=Len(namen(i))).Font.Color = -16776961 '(das ist wohl Excel-intern ROT)
 End If

 ' Der ersten Buchstabe für den nächsten Durchlauf ist der bisherige Anfang,
 ' plus die Länge des eben getesteten Namens plus 1 (wegen dem Chr(10))
 anfang = anfang + Len(namen(i)) + 1
 Next i
 End With
End Sub

Gruß, Andreas

asrue · 14. November 2019 um 14:02

Ups, jetzt erst gesehen. Das geht genau in die richtige Richtung. Vielen Dank dafür. Top wäre es noch, wenn bei der Prüfung und Einfärbung alle beschriebenen Zellen einer Tabelle abgearbeitet werden würden. Hättest Du da noch eine Idee? Momentan ist es so, dass ich jede Zelle einzeln aktivieren und dann die Prüfung machen muss.

D.h. der Name Müller kommt in den unterschiedlichsten Konstellationen (einzeln in einer Zelle oder mit Zeilenumbruch) mehrfach in der gesamten Tabelle vor.

abgemeldet26 · 14. November 2019 um 14:02

Das sollte dann hiermit klappen:

Sub NamenFärben()
 Dim i As Long
 Dim namen As Variant
 Dim suchString As String
 Dim anfang As Long
 Dim zelle As Range

 suchString = "üll"
 For Each zelle In ActiveSheet.UsedRange
 With zelle
 If Not IsEmpty(zelle) Then
 anfang = 1

 ' Wenn die Namen in einer Zelle untereinander stehen, wurden sie mit ALT+Eingabetaste eingegeben.
 ' Dann steht zwischen den Namen immer ein Chr(10)
 ' Im Feld "namen" werden die Namen einzeln abgelegt.
 namen = Split(.Value, Chr(10))

 ' Jetzt werden die Namen nacheinander nach dem Suchstring durchsucht.
 For i = 0 To UBound(namen)
 If InStr(LCase(namen(i)), suchString) \> 0 Then

 ' Wenn ein Name den Suchstring enthält, wird dieser Teil des Zellentextes rot gefärbt.
 ' Der erste Buchstabe, der rot werden muss, wird durch den Wert in "anfang" festgelegt,
 ' die Länge der Rotfärbung ist die Länge des Namens.
 .Characters(Start:=anfang, Length:=Len(namen(i))).Font.Color = -16776961 '(das ist wohl Excel-intern ROT)
 End If

 ' Der ersten Buchstabe für den nächsten Durchlauf ist der bisherige Anfang,
 ' plus die Länge des eben getesteten Namens plus 1 (wegen dem Chr(10))
 anfang = anfang + Len(namen(i)) + 1
 Next i
 End If
 End With
 Next zelle
End Sub

Gruß, Andreas

Niclaus_Wurthberg · 14. November 2019 um 14:02

' Wenn die Namen in einer Zelle untereinander stehen, wurden sie mit ALT+Eingabetaste eingegeben.
' Dann steht zwischen den Namen immer ein Chr(10)
' Im Feld "namen" werden die Namen einzeln abgelegt.
namen = Split(.Value, Chr(10))

Hallo Andreas

SUPER!

Es ist zwar wie verhext: Ich bin überzeugt, beim ersten Versuch hat das Makro geklappt. Bei einem zweiten nicht mehr richtig, trotz Excel- und sogar PC-Neustart: Es wird die ganze Zelle rot geschrieben. - Ich habe nun folgende Aenderung vorgenommen:

namen = Split(.Value, Chr(32))
' _statt Value, Chr(10)_

Und es klappt bestens. Mit einer einzigen Einschränkung:

Wenn ich in einer Zelle (mit Zeilenumbruch) eingebe:

Morgen kommt
Herr Werthmüller
zu uns.

Dann muss hinter „üller“ ein Leerzeichen eingegeben werden, sonst wird das „zu“ auch rot gefärbt.

Ich glaube, generell ist wichtig zu beachten: Die Zelle muss mit Standard formatiert sein. Mit der Formatierung „Buchhaltung“ z. B. geschieht gar nichts.

Deine Version 2 funkt - mit der gleichen Aenderung und „Leerschlag-Einschränkung“.

Vielen Dank und viele Grüsse
Niclaus

abgemeldet26 · 14. November 2019 um 14:02

Hallo Andreas

Hi Niclaus

SUPER!

Danke!

Ich habe nun folgende Aenderung
vorgenommen:

namen = Split(.Value, Chr(32))
’ statt Value, Chr(10)

Und es klappt bestens.

Sorry, das kann ich mir beim besten Willen nicht vorstellen.
Ich habe mal versucht das nachzustellen. Dann stehen nach der Split-Zeile alle Namen aus einer Zelle in namen(0), durch Leerzeichen getrennt.
Dann ist mit auch klar, wieso bei dir die ganze Zelle rot wird:
Wenn namen(0)=„Müller Meier Schulze“, dann findet er darin „üll“ und färbt alles rot vom Anfang bis zum Ende, weil die Länge des Strings mit „üll“ das komplette namen(0) ist.

Mit einer einzigen Einschränkung:

Wenn ich in einer Zelle (mit Zeilenumbruch) eingebe:

Morgen kommt
Herr Werthmüller
zu uns.

Dann muss hinter „üller“ ein Leerzeichen eingegeben werden,
sonst wird das „zu“ auch rot gefärbt.

Ja, das ist klar.

Ich glaube, generell ist wichtig zu beachten: Die Zelle muss
mit Standard formatiert sein. Mit der Formatierung
„Buchhaltung“ z. B. geschieht gar nichts.

Jain. Eigenartigerweise zeigt die Formatierung Buchhaltung die Rötung nicht an. Wenn du aber nachdem der Makro gelaufen ist die Zelle irgend ein anderes Format (außer Buchhaltung) setzt, dann wird die Rötung angezeigt.

Vielen Dank und viele Grüsse
Niclaus

Gruß, Andreas

abgemeldet26 · 14. November 2019 um 14:02

Berichtigung
die Namen in namen(0) nach dem Split sind nicht durch Leerzeichen getrennt, sondern weiterhin durch Chr(10). Macht ja auch Sinn, weil er mit deiner Version in der Zelle kein Chr(32) findet.Es sah nur im Debugger nach Leerzeichen aus. Sind aber Chr(10).

Ich bin mir also ganz sicher, dass deine Version mit Chr(32) nicht richtig funktionieren kann.

Gruß, Andreas

Niclaus_Wurthberg · 14. November 2019 um 14:02

Ich bin mir also ganz sicher, dass deine Version mit Chr(32)
nicht richtig funktionieren kann.

Hallo Andreas

Der Unterschied zwischen Chr(10) und Chr(32) ist mir schon klar!

Ich habe jetzt noch folgende Aenderung im Makro vorgenommen:

With ActiveCell
anfang = 1

' Wenn die Namen in einer Zelle untereinander stehen, wurden sie mit ALT+Eingabetaste eingegeben.
' Dann steht zwischen den Namen immer ein Chr(10)
' Im Feld "namen" werden die Namen einzeln abgelegt.
' _Die folgenden drei Zeilen sind neu_
namen = .Value
namen = Replace(namen, Chr(10), Chr(32) & Chr(10))
.Value = namen

namen = Split(.Value, Chr(32)) ' usw.

Du musst es mir glauben: Ich habe alle möglichen Varianten durchexerziert. Es funktioniert alles bestens!

ABER: Was nicht funktioniert, auch mit Deiner Originalversion nicht: Wenn ich definiere:
suchString = „Müller“
werden die Namen „Müller“ in der Tabelle nicht rot eingefärbt. Ich muss definieren:
suchString = „müller“
also mit kleinen Buchstaben, dann haut es hin. Weisst Du, warum der Suchtext mit „Müller“ nicht funktioniert?

Noch einmal herzlichen Dank - dem „ungläubigen“, aber so oder so „unglaublichen“ Andreas
Niclaus

abgemeldet26 · 14. November 2019 um 14:02

Hallo Andreas

Hi noch mal.

ABER: Was nicht funktioniert, auch mit Deiner Originalversion
nicht: Wenn ich definiere:
suchString = „Müller“
werden die Namen „Müller“ in der Tabelle nicht rot eingefärbt.
Ich muss definieren:
suchString = „müller“
also mit kleinen Buchstaben, dann haut es hin. Weisst Du,
warum der Suchtext mit „Müller“ nicht funktioniert?

Ja, Fehler von mir.
Ersetze mal die Zeile

If InStr(LCase(namen(i)), suchString) \> 0 Then

durch

If InStr(LCase(namen(i)), LCase(suchString)) \> 0 Then

Dann sollte es klappen.

Niclaus

Gruß, Andreas

Niclaus_Wurthberg · 14. November 2019 um 14:02

ABER: Was nicht funktioniert, auch mit Deiner Originalversion
nicht: Wenn ich definiere:
suchString = „Müller“
werden die Namen „Müller“ in der Tabelle nicht rot eingefärbt.
Ich muss definieren:
suchString = „müller“
also mit kleinen Buchstaben, dann haut es hin. Weisst Du,
warum der Suchtext mit „Müller“ nicht funktioniert?

Ich hab’s gefunden: Das LCase() beim letzten If() Deines Makros.
Da habe ich auch die Lösung gefunden für ein anderes (anderas) Anliegen: Ich möchte nur die Namen „Müller“ färben, also nicht auch „Werthmüller“ oder „Freimüller“ usw.:
Dafür lasse ich das LCase weg. Der suchString muss dann aber „Müller“ heissen, nicht „müller“.

Noch einmal herzlichen Dank und viele Grüsse
Niclaus

abgemeldet26 · 14. November 2019 um 14:02

Ich hatte das LCase reingebaut, weil es in dem UP von asrue hieß:
… (und zwar gleich ob groß oder klein geschrieben) …

Gruß, Andreas

asrue · 14. November 2019 um 14:02

Yes, sehr genial. Vielen vielen Dank. Genau wie ich es benötige. Die Problematik der Kompletteinfärbung stellt sich bei mir nicht, da ich bei mehreren Namen in einer Zelle immer nach dem Namen umbreche.

Also nochmal tausend Dank für die schnelle Hilfe.

Niclaus_Wurthberg · 14. November 2019 um 14:02

Ich hatte das LCase reingebaut, weil es in dem UP von asrue
hieß:
… (und zwar gleich ob groß oder klein geschrieben) …

Ich hab nicht nur eine Brille vor den Augen! Manchmal auch ein Brett )
Grüsse Niclaus