I use conditional formatting to identify duplicates. This appeared to be working well but I have just discovered the conditional formatting rule is broken if I copy and paste data rather than enter data.
In the attached worksheet a conditional formatting rule has been applied to the range B2:B13.
If I type the values "Text1", "Text2" and "Text1" in cells B3:B5 the duplication is highlighted.
But if I copy and paste the data from cells B17:B19 to cells B7:B9 the duplication is not highlighted.
If I look at the conditional formatting rule, I see it has been modified to =$B$2:$B$6,$B$10:$B$13 i.e. there is a break where I pasted the data.
However, if I paste the data using Paste Special and select Values, without any format, the duplication is identified and highlighted.
The data in cells B17:B19 does not contain any formatting, and even if I clear any formatting from these cells before copy and paste into cells B7:B9 the duplication is not highlighted.
At the moment I am left with having to use Paste Special but wondering if there is an easier method?
Many thanks in advance