Excel Conditional Formatting - You know you want to help

[Dan~]

Haha, so first time in probably 20 years I need to do something in Excel, I thought I knew how to do it but clearly not, although it's 99.99% working.

All I want to do is highlight unique values except the first in one colour, and highlight all other duplicates to be a different colour.  Example 1001 is there 4 times, so the first time I want it to be green, the other 3 duplicates I want it to be red

 

I've got a column with the following numbers

1000

1001

1001

1001

1001

1002

1002

1003

1004

1004

1005

1005

1006

1006

1007

1007

1007

1007 

 

I setup a conditional formatting to be
=COUNTIF($A$2:$A2, A2)>1 then I select fill cell with a colour, this should be if the value in the cell is not unique then turn field red

I setup another conditional formatting to be =COUNTIF($C$2:$C2, C2)=1 then I selelcted fill cell green, this should be in my mind if unique then turn green.

That almost works but for some reason the very last cell is not changing colour like in the attached photo, any ideas what i'm doing wrong?

 

p.s I need this to work without software