Excel, Pasting text with hyperlinks, conditional formatting for doubles

Question

I have a list of names that I paste, individually, from a site into a column of cells in a table. I have conditional formatting to tell me if there are doubles. However, when I copy straight from the site (each name is actually a hyperlink), the conditional formatting goes away unless I tick the "keep destination formatting", but then the text doesn't have the hyperlink.

I'm aware that you can just keep destination formatting/type the name then add the hyperlink, but this added step takes much more time than simply copying and pasting the hyperlink. Is there a way to solve this problem?

Answer 1

Because the hyperlinking format seems to override the 'Applies to' range for the CF there seems no very simple fix. If you are prepared to reset the Applies to range after copying numerous names (ie not 'in real time') that might be easiest. However you could arrange for the conditional formatting to be in a separate column, so the range is not interfered with. Say use Use a formula to determine which cells to format (at the moment I guess you may be applying the option immediately above, ie Format only unique or duplicate values) and in Format values where this formula is true:

=COUNTIF(A:A,A1)>1

assuming your names are in ColumnA starting in Row1.

The Applies to: here would be the reference to the helper column (say =$B:$B).