Apply Conditional Formatting to other rows

[bizexcelglobal]

Hi all,

I'm using Excel 2007. I would like to seek help regarding conditional
formatting.

What I would like to do is to create conditional formatting to show an
increase/decrease/equal arrow if the cell is greater/less than/equal
to the cell beside it. For example, if cell A2 is greater than A1, an
up arrow will appear in A2. And if cell A3 is less than A2, a down
arrow will show in A3.

Excel does not allow me to use relative cell referencing for
conditional formatting. So currently i have to do conditional
formatting manually for every cell. I have a few hundred rows and
columns. Is there a way I would be able to create a conditional
formatting so that can be applied to all the cells?

Thanks in advance.

 

[Jarek Kujawa]

why "Excel does not allow me to use relative cell referencing for
conditional formatting"?
it does not allow you to insert relative addresses by hand? or using
F4?

 

[OssieMac]

I don't really understand what you want to do with these arrows. I didn't
think that fonts could be set with conditional format to get an arrow from
Windings or whatever in a cell.

Anyway, your other part about the absolute/relative addressing when setting
conditional format. I find the easiest way to apply conditional format to a
range is as follows:-

Select the entire range where the conditional format is required.

Set the formula or condition as if it applies only to the first cell of the
selection. (That is the cell that is not shaded.)

In xl2007, if you use the option "Use formula to determine which cells to
format" then start with the = sign and then enter the formula which must
evaluate to True to set the format or False to not set the format. If you use
the mouse to click on required cell/range to insert cell addresses in the
formula then they usually appear as absolute with the $ signs. If you want
relative addressing then just edit out the $ signs (delete them).

When you exit from the Conditional formatting dialog box, Excel applies the
condition to all of the selected cells.

If this does not answer your question then post the info that you are using
to set the format for just one cell. Include step by step what you are doing.

 

[Jacob Skaria]

You can have different incentive slabs like 0-25000,25000-40000,40000 and above
A1 = total amount

=LOOKUP(A1,{0,25001,40001},{0,1000,1500})

If this post helps click Yes

 

[bizexcelglobal]

Hi there. Thanks for your help.

What im actually using is conditional formatting> icon sets> 3 arrows
(coloured). The rule type im using is 'Format all cells based on their
values'. What i would do is to select a cell (A2) and format it so
that, when its value is greater than (A1) then a green up arrow will
appear in A2. And when it is equal to A1 then it will show a yellow
right arrow. and if it is less than it will show a red down arrow.
Excel would not allow me to put in just '=A1', so i would have to
enter a fixed cell (=$A$1). In this way i would not be able to auto-
fill or copy and paste format to other cells. as it would all be fixed
at A1 cell, which is not what i want. What i want to compare would be
column B values with column A. and Column C values with column B.

 

[bizexcelglobal]

Hi Jarek

Thanks for your reply. Excel would not me to insert relative address
by hand as well as using F4. What im actually using is the icon sets
(3 arrows).

 

[Jacob Skaria]

Sorry the below is a wrong post