Differentiate duplicate values in excel

[cjay123]

I was hoping someone could assist me with this. I using =COUNTIF($Y$1:$Y$200000,$Y1)>9 in a conditional formula to find duplicates that appear 10 or more times in column Y. I was wondering if it is possible to make each set of duplicates a different color?

[aganesan99]

Hi cjay123,

Please find my solution in my attached file.

Thanks,
aganesan99

[cjay123]

The numbers im working with are random. Im sure this will require VBA