Conditional Formatting - Please Help!!!

[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.

 

[Bob Phillips]

I am not sure whether you want the check in C1 to be C1<A1 or B1<A1, but
play with versions of this

B$1<$A$1

change the $ until it is what you want.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I want the check in C1 to be C1<A1 but when I copy the conditional format
from B1 and paste into C1 the conditional formula stays as B1<A1. I then have
to manually change it to be C1<A1. As I have a lot of these to do I want to
try and make excel reference the current cell in the conditional format
formula to compare with A1. Hope this makes sense.

 

[Bob Phillips]

Then use

=B1<$A1

in B1 and copy

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

you are a scholar and a gentleman thanks Bob!

Then use

=B1<$A1

in B1 and copy

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)