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

K

kboekhoff

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!
 
P

Pete_UK

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
 
K

kboekhoff

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top