I'm having a table (in this example) everyone should write their name behind the Fruit he likes most.
I'm creating a dropdown box based on all people who wrote their names anywhere. Based on the Name picked of this dropdown (B5), I want the color of the cell to change based on the most liked fruit. So now since I picked Mike I want this cell to color green. If I'd pick Steve I want it to change to yellow.
In my thoughts it should be something like: if text matches B2:X2 then color green else if text matches B3:X3 then color yellow
Update: Since I got this first question resolved, I now faced a different problem. When I have more then one of those dropdown boxes my rule doesn't work like expected.
On B7 it's working but since I copy and pasted this dropdown a couple times it doesn't work like expected. Sow my final goal would be to have a second table full of dropdown boxes with all names from table one. The conditional formatting I'd like to achieve here is:
If any dropdown box (B7,B8,B9,C7,C8,C9) got any name selected, color the cell green/yellow depending on where the name is in the first table.