Macro to Count Duplicate Values

I have checked with a bit of code and:

It looks like Excel doesn't recognise the fact that the color is changed by a conditional format.

You may have to check for duplicates in Visual Basic

.Interior.Color returns the "real" color, not the conditionally-formatted color result..