Excel 2007 : Conditional Formation duplicates within range with 1st match differnet color

[dave_gordon]

Hi there,
I searched for ages and cannot come up with an answer whereby I have a range of cells (D3<M110) and i want to highlight the duplicates but for the first match make the highlight a seperate colour.

I can do this using two seperate conditions
a) for one column (D) using;
=IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)=1)
=IF(COUNTIF($D:$D,$D1)>1,COUNTIF($D$1:$D1,$D1)>1)

but when I try
b) for a range (D3>M110) uisng;
=IF(COUNTIF($D$3:$M$110,D3)>1,COUNTIF($D$3:$M$110,D3)=1)
=IF(COUNTIF($D$3:$M$110,D3)>1,COUNTIF($D$3:$M$110,D3)>1)

It highlights all the duplicates, and the condition that should seperate the first match does not work.

Can anybody help?

[Mallycat]

Your second and fourth instance of D3 are missing an absolute reference. I think this is required to do what you are trying to do.

[dave_gordon]

I think I tried this but and although it worked it highlighted the entire row.

I managed to find another solution that uses the following forula to change the 2nd or any more duplicated

=IF(COUNTIF($D$3:$M$110,D3)=1,FALSE,NOT(COUNTIF($D$3:D3,D3)=1))

and I then change the first instance using the normal conditioning (if <> "")

Many thanks for your efforts

Regards
Dave

[dave_gordon]

Sorry, I've actually realised that what I thought was a solution does not work.

I've also tried Mallcat's recomendations but this also does not work.

Any other suggestion would be much apprechiated

Regards
Dave