In einer Liste sollen alle Zeilen mit identischen Inhalten aus Spalte A gleich formatiert werden. Wechselt der Inhalt, wechselt auch automatisch die Farbe. Dann soll so lange die gleiche Farbe pro Zeile genommen werden, wie sich dieser Inhalt in Spalte wiederholt.

Gelöst wird das Problem mit zwei Formeln in der bedingten Formatierung. Bei YouTube wird noch einmal das Problem beschrieben.

Hier ist die Fragestellung auf YouTube:

Videolink: http://youtu.be/67CIoTslVBY

Von den vielen Lösungsmöglichkeiten möchte ich die geniale Lösung von Andreas Entenmann (Excel MVP) beschreiben.
Für den kompletten Wertebereich A2:Cxxx wird eine Prüfung auf das Ergebnis einer Formel in der bedingten Formatierung hinterlegt.

1. Zunächst wird der Bereich markiert
2. Anschließend START – Formatvorlagen – Bedingte Formatierung – Neue Regel
3. Dann auf Formel zur Ermittlung der zu formatierenden Zelle verwenden klicken
4. Folgende Formel hinterlegen:
=REST(SUMMENPRODUKT(N($A$1:$A1$A$2:$A2));2)=1
5. Eine Formatierung für den Fall hinterlegen, dass bei der Prüfung der Wert WAHR resultiert.
6. Dialog schließen

Den gleichen Vorgang für =REST(SUMMENPRODUKT(N($A$1:$A1$A$2:$A2));2)=0 mit einer anderen Formatierung wiederholen.

Doch was passiert hier in der Formel?
Es werden die Funktionen N(), REST() und SUMMENPRODUKT() genutzt. Dazu noch durch den Bereichsbezug $A$1:$A1 bzw. $A$2:$A2 ein mit jeder Zeile anwachsender Bereich.

Die Funktion REST(Zahl;2) teilt eine Zahl durch 2. Es wird aber nicht geschaut, wie oft die Zahl 2 in die Zahl passt, sondern welcher Rest nach der Division übrig bleibt. Bei geraden Zahlen resultiert ein Rest von 0, bei ungeraden Zahlen ein Rest von 1. Das Ergebnis von Rest wird in der bedingten Formatierung genutzt.

N() wandelt den Wert WAHR in eine 1, Text und den Wert FALSCH in den Wert 0 um, Zahlen behalten Ihren Wert. Befindet sich eine Prüfung in den Klammern von N() so z.B. N(8>=4), dann wird N(WAHR) = 1 gerechnet. Hier wird geprüft, ob der Wert der Vorzelle aus Spalte A anders ist als der Wert der aktuellen Zelle in Spalte A aus der gleichen Zeile. Also z.B. N(A4A5) oder N(A1A2). Immer dann, wenn also praktisch eine Zeile höher der gleiche Wert steht, kommt bei dieser Funktion der Wert 0 heraus, ansonsten eine 1.

SUMMENPRODUKT(Array1;Array2;…) bildet die Summe seiner einzelnen Produkte. Da aber nur ein einziges Array existiert, gibt es nichts zu Multiplizieren, sondern lediglich zu Addieren. Und was wird addiert? Der Wert der jeweiligen Prüfungen eines Bereichs, ob die Werte eines Bereichs mit den Werten eines anderen Bereichs verschieden sind. Und zwar jeweils beim Vergleich von einer Zelle in Spalte A zur Zelle darüber. Das nur in Spalte A geschaut wird, wird durch die $-Zeichen vor dem jeweiligen A garantiert.

Bei der Formatierung wird also pro Zelle geprüft, wie viele unterschiede von aktueller Zeile Spalte A gibt es bis zur Zelle A1 zurück. Liegen identische Werte untereinander, wird dieser Wert nicht erhöht. Wechselt aber ein Eintrag in der aktuellen Zeile, wird um 1 erhöht und die Funktion REST wird letztendlich zu einem anderen Ergebnis kommen.

Hier ist die Lösungsdatei:
Excel 467 – Excel Lösung wechselnde Zeilenfarbe

Und hier ist die Lösung auf YouTube:
Videolink: http://youtu.be/qv7fjEmuMcA