# Conditional Formatting based on difference in Cells

1. ## Conditional Formatting based on difference in Cells

Hi, apologies if this has been posted before but my searches have not provided a solution.

I am trying to create a conditional formatting rule that can establish the difference between 2 cells (e.g. Cell 1 = 50% / Cell 2 = 60%) so that if the difference is greater than 10% it goes red/green (if minus/plus).

The closest I can think of would be to create a "difference" cell, however I would like the conditional formatted colour to apply to the original cells.

Hope that made sense! Any help will be greatly appreciated as always!!

Thanks.

2. ## Re: Conditional Formatting based on difference in Cells

use the formula option in conditional formatting and put
=ABS(\$A1-\$B1)>0.1
that will just satisfy if the difference is greater than 10%
when you say -/+ i assume thats relative to say a1
=AND(SIGN(\$A1-\$B1)=-1,ABS(\$A1-\$B1)>0.1) red
=AND(SIGN(\$A1-\$B1)=1,ABS(\$A1-\$B1)>0.1) green

3. ## Re: Conditional Formatting based on difference in Cells

In Conditional Formatting rules..

=ABS(\$D\$1-\$C\$1)>10%

4. ## Re: Conditional Formatting based on difference in Cells

That's great, thank you both so much! I'm going to be annoying though (plus show my primitive knowledge even further) and ask how to render a 2nd format that will assign a different number if the difference is a negative percent?

i.e. Cell 1 = 50% / Cell 2 = 30% - Currently this will be formatted to Green. If Cell 1 = 50% / Cell 2 = 70% - I need Cell 1 to now be red as Cell 1 is <10% of Cell 2.

Thank you again!!

Try

=d1-c1<0

6. ## Re: Conditional Formatting based on difference in Cells

didnt the 2 i posted do just that?
=AND(SIGN(\$A1-\$B1)=-1,ABS(\$A1-\$B1)>0.1) red
=AND(SIGN(\$A1-\$B1)=1,ABS(\$A1-\$B1)>0.1) green

7. ## Re: Conditional Formatting based on difference in Cells

Originally Posted by martindwilson
didnt the 2 i posted do just that?
=AND(SIGN(\$A1-\$B1)=-1,ABS(\$A1-\$B1)>0.1) red
=AND(SIGN(\$A1-\$B1)=1,ABS(\$A1-\$B1)>0.1) green
It did! But I was too slow to figure out how to use it so accurately!!

Thank you again so much, this is exactly what I needed.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1