Conditional Formatting

[Guest]

Hi everyone, please can you apply your grey matter to this one for me?

I am using conditional formatting in excel 2007 in the following manner.

I have data that looks something like this:

A B C D
1 95 50 65 95
2 95
3 95

Cell A1 represent a target value that I want to compare B1 with, if B1 < A1
then I want to shade the cell B1 red, else green. I have done this with no
difficulty.

The problem occurs when I want to copy and paste the conditional format to
cell C1. I do not want to manually apply a conditional format to every cell
on my sheet (there are lots) but would like to be able to copy+paste the
format to other cells and to have excel change the formula (criteria for the
format) to compare the current cell with the target. So in the example above
no matter what I try a paste results in C1 being formatted on the basis of a
comparison between B1 and A1, Excel does not seem to transpose the formula.

Any help / advice will be greatfully received.
Thanks
Steve.

 

[Guest]

As I understand it, you want all designated cells formats to be controlled by
the comparison between B1 and A1?

Two options:
Select all cells that are to be affected and set up the conditional
formatting formulas using absolute reference as (just one formula shown)
=$A$1<$B$1

Another option would be to use the 'Format Painter' (looks like a paint
brush) to copy the formatting (including conditional formatting) from a cell
with it set up to other cells. The initial cell must be set up with the
absolute addressing also, if that's the way you want the comparison to be
carried out.