Conditional Formatting to higlight the second time a result is returned in a row

[saraapple]

I have a table of data with some repeated values across the rows:

Route	PAR	Start Time	Depot	D1	D2	D3	D4	D5	D6
1	PAR-00735076	03:00 Tue	DHLSTK	KIRKCALDY	EDINBURGH	DUNDEE	ABERDEEN	DUNDEE	ABERDEEN

I want to format the row so that only the second time the value is repeated the cell is highlighted:

Route	PAR	Start Time	Depot	D1	D2	D3	D4	D5	D6
1	PAR-00735076	03:00 Tue	DHLSTK	KIRKCALDY	EDINBURGH	DUNDEE	ABERDEEN	DUNDEE	ABERDEEN

Can I achieve this through conditional formatting?

Many thanks
Sara

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Route	PAR	Start Time	Depot	D1	D2	D3	D4	D5	D6
2	1	PAR-00735076	03:00 Tue	DHLSTK	KIRKCALDY	EDINBURGH	DUNDEE	ABERDEEN	DUNDEE	ABERDEEN
3	1	PAR-00735076	03:00 Tue	DHLSTK	KIRKCALDY	EDINBURGH	STRILING	PERTH	PERTH	ABERDEEN
4	1	PAR-00735076	03:00 Tue	DHLSTK	KIRKCALDY	EDINBURGH	DUNDEE	ABERDEEN	STIRLING	EDINBURGH
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:J4	Expression	=COUNTIFS($E2:E2,E2)>1	text	NO

 

[tyija1995]

Hey do you mean like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Route	PAR	Start Time	Depot	D1	D2	D3	D4	D5	D6
2	1	PAR-00735076	03:00 Tue	DHLSTK	KIRKCALDY	EDINBURGH	DUNDEE	ABERDEEN	DUNDEE	ABERDEEN
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:J2	Expression	=COUNTIF($A$2:A$2,A$2)=2	text	NO

 

[saraapple]

Got it working - thank you for taking the time

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[saraapple]

One last thing - could I add an AND function to ignore the blank cells? At the moment when I extend the range, the first blank is left then the colour is applied to those thereafter when I would like them to remain unchanged as they are blank.

 

[Fluff]

How about

=AND(E2<>"",COUNTIFS($E2:E2,E2)>1)

 

[saraapple]

Perfect!

 

[Fluff]

My pleasure.