Conditional Formatting - change colour based on a +/- %

**Andy101** · 10-03-2009, 04:06 PM

Hi

I want to change the colour of a cell based on the following conditions:-

= A1 & B1 are the same = Green

= A1 is within + or - 10% of B1 = Amber

= A1 is greater than + or - 10% of B1 = Red

I'm using Excel 2002 so I can only have 3 rules.

Any ideas? I had a quick look at http://chandoo.org/wp/tag/conditional-formatting/ and I couldn't see anything...

**zbor** · 10-03-2009, 04:19 PM

Something like this?

Book1.xls

Actually you need 2 CF, not 3.

**teylyn** · 10-03-2009, 04:23 PM

Hi, your three conditions can be

1. Formula is
=A1=B1

2. Formula is
=ABS((B1-A1)/B1)<=0.1

3. Formula is
=ABS((B1-A1)/B1)>0.1

**zbor** · 10-03-2009, 04:29 PM

No need for 3 CF and slowing down workbook. 2 are enough:

1. CF - green when equal
2. CF - yellow when <=+-10%
3. no CF - red, everything else

Just example, any other combination can be made

**teylyn** · 10-03-2009, 04:29 PM

@zbor: The OP is after a solution that looks if the numbers are within 10% of each other. Your solution is based on a difference of 10 between the two instead.

So, A=1000 and B=1020 should be amber, because the difference is less than 10%, but your solution shows it red because it's more than 10.

Slight difference.

zbor is right with regards to the two conditions only, though. If your default color for the range is green, then you only need to define the rules for amber and red.

cheers