copying a conditional format gives the whole range

[D.D.]

I wish to have many rows with a conditional format between two adjacent
cells, i.e. A1 & B1, A2 & B2 ( column B has the conditional format depending
on the value of column A ). I can copy the condition ( using format
painter ) and paste to B2. I can paste to B3, B4 etc. However if I want to
paste several cells at once, the relative value in each cell in column B
changes to an absolute value of the whole range of data, ie. $B$2:$B$4. The
reference for column A does not change, so each cell in column B from B2
onwards has the whole range and conditional on the one cell ( A2 ). I've
tried manually entering the cell references =B2 =B3, but no luck. Any ideas
?

 

[T. Valko]

I've never used the format painter...

So, you want to format column B based on a condition of column A? What is
that condition?

 

[D.D.]

Cell A1 and B1 contain numerical values. Cell B1 has the condition - if A1
is equal or less than B1, the font colour in B1 is Red, if A1 is greater
than B1 the font colour of B1 is Yellow. I just then want to copy the
condition to B2 and B3 down to cell B100. In cell B100, instead of getting
the condition
- if B100 =< A100 font for B100 is red, B100 >A100 font for B100 is yellow
I get - if $B$2:$B$100 =<A2 font for B100 is red, $B$2:$B$100 >A2 font for
B100 is yellow

The value in the condition for B2 is set as relative, but excel changes it
to absolute for some reason, as well as giving the whole range eg.
$B$2:$B$100. I used to use a previous version of Excel with no problem, but
changing to 2007 and copying formats do not seem to work.

 

[T. Valko]

changing to 2007 and copying formats do not seem to work

Well, I don't have Excel 2007 so I guess I can't help. Sorry!