# 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.  Register To Reply

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  Register To Reply

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

In Conditional Formatting rules..

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

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!!  Register To Reply

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

Try

=d1-c1<0  Register To Reply

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  Register To Reply

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.   Register To Reply