How do I set conditional formatting relative to another cell -XL20
 

I want to conditionally format a cell - say cell B1 - based on the value in
another cell - say cell A1 - and then apply that relative formula to a column
of values:

if B1 < A1 then turn red
if B1 <= (A1 * 0.90) then turn yellow

I can create the rules for this (red rule gets priority over yellow rule) in
the new Excel 2007, but I can not figure out how to copy down the rule to
cells B2 (compared to cell A2), B3 (compared to cell A3), etc.

In the older version of Excel I could just copy and paste special --
formats, but in this version, it keeps putting the $ in front of the
variables (i.e. $A$1) fixing them in place. (It won't let me delete the $
signs; it adds them back in whenever I click "apply".) I have a column of
several hundred values and don't really fancy adding each of these rules to
all of the values!

Any advice would be greatly appreciated! Thanks!

How do I set conditional formatting relative to another cell-XL20
 

Highlight all the cells in column B first, with B1 as the active cell,
and then apply your CF rules as if for B1 - Excel will adjust the cell
references for you when you OK out.

Hope this helps.

Pete

On Dec 7, 4:37*am, kboekhoff
wrote:
I want to conditionally format a cell - say cell B1 - based on the value in
another cell - say cell A1 - and then apply that relative formula to a column
of values:

if B1 < A1 then turn red
if B1 <= (A1 * 0.90) then turn yellow

I can create the rules for this (red rule gets priority over yellow rule) in
the new Excel 2007, but I can not figure out how to copy down the rule to
cells B2 (compared to cell A2), B3 (compared to cell A3), etc.

In the older version of Excel I could just copy and paste special --
formats, but in this version, it keeps putting the $ in front of the
variables (i.e. $A$1) fixing them in place. (It won't let me delete the $
signs; it adds them back in whenever I click "apply".) I have a column of
several hundred values and don't really fancy adding each of these rules to
all of the values!

Any advice would be greatly appreciated! Thanks!

How do I set conditional formatting relative to another cell -
 

Unfortunately, that did not work. It is still only doing conditional
formatting based on cell A1 for all selected cells...

"Pete_UK" wrote:

Highlight all the cells in column B first, with B1 as the active cell,
and then apply your CF rules as if for B1 - Excel will adjust the cell
references for you when you OK out.

Hope this helps.

Pete

On Dec 7, 4:37 am, kboekhoff
wrote:
I want to conditionally format a cell - say cell B1 - based on the value in
another cell - say cell A1 - and then apply that relative formula to a column
of values:

if B1 < A1 then turn red
if B1 <= (A1 * 0.90) then turn yellow

I can create the rules for this (red rule gets priority over yellow rule) in
the new Excel 2007, but I can not figure out how to copy down the rule to
cells B2 (compared to cell A2), B3 (compared to cell A3), etc.

In the older version of Excel I could just copy and paste special --
formats, but in this version, it keeps putting the $ in front of the
variables (i.e. $A$1) fixing them in place. (It won't let me delete the $
signs; it adds them back in whenever I click "apply".) I have a column of
several hundred values and don't really fancy adding each of these rules to
all of the values!

Any advice would be greatly appreciated! Thanks!