Excel 2007 : Conditional formatting-when i use this formula?

**rebeccaneeves** · 07-24-2009, 11:37 AM

Hi
I want to conditionally format a cell based on value of another cell, details:

If cell A1 = 1, conditionally format cell B1
fill red if <9
fill yellow if 9-12
fill green if >12

If cell A1 = 2, conditionally format cell B1
fill red if <15
fill yellow if 15-16
fill green if >16

If cell A1 = 3, conditionally format cell B1
fill red if <17
fill yellow if 17-20
fill green if >20

If cell A1 = 4, conditionally format cell B1
fill red if <21
fill yellow if 21-22
fill green if >22

If cell A1 = 5, conditionally format cell B1
fill red if <23
fill yellow if 23-26
fill green if >26

If cell A1 = 6, conditionally format cell B1
fill red if <27
fill yellow if 27-30
fill green if >30

would appreciate any help, Thanks
rebecca

**martindwilson** · 07-24-2009, 02:50 PM

as you have 2007 you can do this by adding them in conditional formatting on the home tab
choose new rule then use a formula... option and add all 18 rules one at a time in this format
=AND($A1=1,$B1<9) red
=AND(A$1=1,$B1>=9,$B1<=12) yellow

=AND(A$1=1,$B1>12) green
then use the same structure for if A2= and so on

nb you may want the less than ones not to include 0
change to
=AND($A1=1,$B1>0,$B1<9) red

**Paul** · 07-24-2009, 02:58 PM

Hello Rebecca, and welcome to the forum!

You will need to create 18 conditional formatting rules for this to work. Luckily you can do that many in Excel 2007, but it won't work in Excel 2003 or earlier - for that you'd need to use a macro and just set the background fill color based on your conditions.

In 2007, select cell B1 and click Conditional Formatting -> New Rule... -> select 'Use a formula to determine which cells to format' -> in formula box type the appropriate formula, click Format to set the background color and click OK twice.

Examples:

If cell A1 = 1, conditionally format cell B1
fill red if <9
Use formula: =AND(A1=1,B1<9)
fill yellow if 9-12
Use formula: =AND(A1=1,B1>=9,B1<=12)
fill green if >12
Use formula: =AND(A1=1,B1>12)

Create the other 15 formulas for when A1 and B1 are different. Hopefully you can see the pattern on how to do so now.

**shg** · 07-24-2009, 03:47 PM

If you wanted to do it with just two formats, and be compatible with your Excel 2003 friends, then format the cell as yellow, and in some convenient place, put a table:

      -C- -D-
  1   min max
  2     9  12
  3    15  16
  4    17  20
  5    21  22
  6    23  26
  7    27  30

Then the format conditions for B1 are:

Condition 1: cell Value Is, less than, = INDEX($C$2:$C$7, $A$1), and format as red

Condition 2: cell Value Is, greater than, = INDEX($D$2:$D$7, $A$1) and format as green

Then you could change the breakpoints without editing formulas.