ExcelBanter - Conditional Formatting

Hi Zubbus! I'm not one of these Excel whizzes, but I played around with
this, and found a solution for you. I'm sure there are more "elegant" ones
around, but this one does not involve macros or programming....and it's
actually quite simple (don't let the length of this answer fool you - it's
VERY simple). OK, here it goes:.

FOR THIS EXAMPLE, ASSUME THE FOLLOWING:

Row 5 is the row where you want the 5 highest values highlighted)
Cells A1, B1, C1, D1, E1 is where we're going to "hide" five simple formulas
that return the five highest values (you can write these anywhere on your
worksheet, and lock them up)

FORMULAS FOR RETURNING THE FIVE HIGHEST VALUES:

In cell A1 write in the following formula: =LARGE(5:5,1)
In cell B1 write in the following formula: =LARGE(5:5,2)
In cell C1 write in the following formula: =LARGE(5:5,3)
In cell D1 write in the following formula: =LARGE(5:5,4)
In cell E1 write in the following formula: =LARGE(5:5,5)

Each of the formulas above return the first highest value in row five, the
first highest, the second highest, the third...etc.
Make sure you protect these cells to ensure that these formulas are not
accidentally erased or modified.

NOW FOR THE CONDITIONAL FORMATTING:

Select cell A5
Go to the Conditional Formatting menu, and select "formula is" in the first
box
In the formula space write the following:

=OR(A5=$A$1,A5=$B$1,A5=$C$1,A5=$D$1,A5=$E$1)

which means, "if this cell value is equal to any of the five largest values)
IMPORTANT: make sure there are NO "$" in front of the A (we want to be able
to copy this formatting to the whole row and have it adjust to each cell),
conversely make sure there ARE "$" in front of the references to the cells
that contain the formulas.

NEXT: select the formatting you want if the cell contains any of the five
highest values.

EXTENDING THE CONDITIONAL FORMATTING TO THE WHOLE ROW:

OK, pretty simple now.. just copy the formatting with the format painter,
select the whole row, and paste!

Any cell that matches the five highest values will now change to your chosen
format, and will change according to changes in the data.

Stilla

"Zubbus" wrote:

Hi!

Is there a way (even a long way) to apply conditional formatting based
on order, say:

I want to paint bold blue the 5 highest values in a row?

Thanks.

--
Zubbus
------------------------------------------------------------------------
Zubbus's Profile: http://www.excelforum.com/member.php...o&userid=29875
View this thread: http://www.excelforum.com/showthread...hreadid=495792