Conditional Formatting by percent range

  • Thread starter Thread starter Sandusky
  • Start date Start date
S

Sandusky

Windows XP Pro SP3
Excel 2007

Suppose cells A1:A10 contain integer values. Now suppose cells B1:B10
also contain integer values. I'd like to conditionally format the
values in column B so that if the value in column B is greater than
100% of the value in column A then the B cell is shaded red. If the
value in column B is greater than or equal to 75% but less than 100%
of the value in column A then the B cell is shaded yellow. If the
value in column B is less than 75% of the value in column A then the B
cell is shaded green. Is this possible?

A B
=============
20 18 <-- shaded yellow
50 10 <-- shaded green
75 81 <-- shaded red
 
You can use the Conditional Formatting feature.

Click in target cell (B1), then click Format>Conditional Formatting.

Under Condition 1 have 'Cell Values Is', then 'greater than', then =B2, then
click the Format button and on the Patterns tab select the red shade, and
click OK.

Back at the Conditional Formatting dialogue, click Add>> and a Condition 2
set of boxes appear. Set these to 'Cell Value Is', between, =0.75*B2, and
=B2, then select the yellow shading.

Add>> again and set Condition 3 as 'Cell Value Is', less than, =0.75*B2,
then select the green shading, and OK twice.

Finally copy cell B1 (Ctrl + C), then highlight cells B2:B10, right click,
select Paste Special>Formats.

That should do it.

Regards,

Tom
 
I have something similar to the question above. Instead of have multiple cells changing, I have one cell changing based on a percentage of another cell:

A1= if B1
======================
100 =100 or (-+)10% <-- shaded green
100 Between (-+)10% and (-+)20% <-- shaded yellow
100 greater that (-+)20% <-- shaded red
 
Back
Top