Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 782
  • Last Modified:

Excel - Conditional format rule is broken after using copy + paste

Hi

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
Alison
Example-with-duplicates-conditio.xls
0
alisonthom
Asked:
alisonthom
  • 3
  • 2
1 Solution
 
Steven HarrisPresidentCommented:
The default setting is to keep formatting, in this instance, you are bypassing the the conditional formatting.

Which version of Office are you running?

You can try using Alt > e > s > v > enter
0
 
alisonthomAuthor Commented:
Thanks for the quick reply.  I am using Excel 2010.
0
 
Steven HarrisPresidentCommented:
You can use the Alt > e > s > v > enter method, or you can also set a QAT item with the preferred paste option.
0
 
alisonthomAuthor Commented:
I like the QAT suggestion - a very neat solution indeed.  Thank you!
0
 
Steven HarrisPresidentCommented:
Not a problem, and thank you!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now