CF help please.

[serge]

Hi,
I'm looking for a CF formula that will highlight duplicate per row ( Not per column ) for those 5 numbers each time, if possible different color for different numbers which are 13 of them ? Maybe that not an option ? Thank you.
Serge.

 

[Fluff]

How about
=COUNTIF($B4:$F4,B4)>1

To have different colour, you would need to setup a rule for each number.

 

[serge]

Thank you Fluff, I appreciate your help.
Serge.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Eric W]

If you only have 5 columns, you would have a maximum of 2 colors per row. If you want to highlight matching colors on each row, not necessarily a given color for a given value, you can try this:

Book1
	A	B	C	D	E	F
4	1/1/2020	3111	1212	2211	1131	1212
5	1/2/2020	2121	1311	2112	2121	2112
6	1/3/2020	2112	1221	2121	1131	1112
7	1/4/2020	1121	2211	1212	2211	1122
Sheet29

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:F7	Expression	=(COUNTIF($B4:$F4,B4)>1)*(MATCH(B4,$B4:$F4,0)=AGGREGATE(15,6,MATCH($B4:$F4,$B4:$F4,0),4))	text	NO
B4:F7	Expression	=COUNTIF($B4:$F4,B4)>1	text	NO

Note that the order of the rules is important. Make sure you enter them in this order, or adjust the order with the "Manage Rules" box.

 

[serge]

Thank you Eric, The formula dont work, I'm sure it's right but it's because maybe because Excel 7 dont have " AGGREGATE " in the system ?
But Thank you for the help.
Serge.

 

[Eric W]

Sorry, I didn't notice your version. Try this instead:

=(COUNTIF($B4:$F4,B4)>1)*(MATCH(B4,$B4:$F4,0)=LARGE(MATCH($B4:$F4,$B4:$F4,0),4))

 

[serge]

Very Nice, it works fine, Thank you very much for the help.
Serge.

 

[CA_Punit]

@Eric W .

Superb Sir..

 

[Eric W]

Glad we could help! ?