Conditional Formatting

[tryingtoexcelatexcel]

Hi All,

Just have a quick conditional formatting question. I have a table with rankings that I would like to color the cell red if the ranking is negative. I have two tables one that ranks the destination (Table B3:Z10) and another that contains all each destination's operating profit or loss.

For the profit and loss rankings I know how to color each cell red, just use any value less than 0 is red but for the table that ranks each destination (B3:Z10) I would like to also color these cells red if it is an operating loss and no color if it is profit.

Thanks in advance.

[6StringJazzer]

Select C4:Z10. Go into CG and Add New Rule then use "Use a formula to determine which cells to format." Use the formula

=C16<0

then apply your formatting.

If you have any trouble let me know and I can do it in your file for you.

[tryingtoexcelatexcel]

Ah ok, thanks Jeff. I thought I had to somehow match the range C4:Z10 with the range C16:Z22.

[6StringJazzer]

Conditional formatting formulas are relative to the upper left cell of the range being formatted. If you are formatting the range C4:Z10, you should write the formula relative to C4. So if you use the formula I gave above

=C16<0

it will be interpreted relative to all cells in the range. That is, C4 will be formatted if C16<0, D4 will be formatted if D16<0, C8 will be formatted if C20<0 and Z10 will be formatted if Z22<0. This is one aspect of conditional formatting rules that is not explained very well in Excel Help.