Count occurrences and returns color

[VincentDraghi]

Hello

Can anyone help me with a formula or a VBA script ?
There are 4 columns with many possible choices (Alice,Bob,Clarisse,Denis,Ernest...). The whole idea is to let 4 teams (columns) to pick a name as representative and check if they do agree on a name or not
I would like to count the number of name and return a color.
For instance, if in a row, there are 4 "Alice" or 3 "Alice", it should return green color cell
IF there are only 2 "Alice", it returns orange color cell
To finish with, if a row contains less that one "Alice", it will return a red color cell.

I tried some formulas with "IF" and "CountIF", but cannot apply it to the whole sheet since the names are all different (mostly names).

The colored cell goes to the corresponding row from the table "Result"

Thanks in advance

[#2]

@VincentDraghi 

Should this be specific for Alice, or do you want to color a cell green if any name gets 3 or 4 votes?

[#3]

@Hans Vogelaar 

Hello !

Yes this is not specific to Alice but any name since there are 33 different names.

[#4]

@VincentDraghi 

Let's say  that the choices are in columns A to D, starting in row 2 and that you want to color F2 and down.

Select the range in column F that you want to format. F2 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=COUNTIF(A2:D2,INDEX(A2:D2,1,MODE.SNGL(MATCH(A2:D2,A2:D2,0))))>=3

Click Format...

Activate the Fill tab.

Select green.

Click OK, then click OK again.

Repeat these steps, but with the formula

=COUNTIF(A2:D2,INDEX(A2:D2,1,MODE.SNGL(MATCH(A2:D2,A2:D2,0))))=2

and orange.

Finally, repeat them again with the formula

=AND(COUNTA(A2:D2),COUNTIF(A2:D2,INDEX(A2:D2,1,MODE.SNGL(MATCH(A2:D2,A2:D2,0))))=0)

and red.

[#5]

Thanks a lot, i will try it out !