Multiple conditions (up to 3) CAN be combined. The first condition that is
satisfied gets applied.
As for the 2nd and 3rd largest, replace MAX(range) with LARGE(range,2) and
LARGE(range,3).


"Frank Drost" wrote:

That is brilliant! Thanks a lot. It makes my table look much easier. The real
dataset is well over 30000 lines.
Maybe I am pushing it a bit too much, but is it also possible to have an
extra statement in the CF that checks that if the value in a cell is the
second highest value in that row, under the condition that column k is less
than 50, gets a colour green. And then for the third highest value, under the
same conditions, the colour yellow.
I see that under CF I can click on the button "add" and that gives me too
more CF possibilities. Can they be combined? And how do I find the second and
third highest value in the row?

This might be not possible in EXCEL, but hey, I have learned some new tricks
now, so who knows this might be possible after all.

ta


"bpeltzer" wrote:

Change the CF condition to read: cell value is equal to
=IF($k7<50,MAX($A7:$J7),A7-1). (That's the condition for cell A7, assuming
the range considered for the max is in columns A:J and the threshold value is
in column K; as before copy the CF to your entire table.) The logic in the
equation is: if the value in column K is less than 50, format the cell if it
is the row's max. If the value in column K is =50, format the cell if it is
equal to one less than the cell (that is, never!). --Bruce

"Frank Drost" wrote:

Great! That worked. I missed the trick of paste special with the format radio
button.
But just to make it slightly more complicated, what if I want to have the
same criterium, but with an extra statement in it that the value in column 10
(K) should be less than e.g. 50? So, the cell will only be coloured red IF it
is the highest number of the row and IF the value in column k is less than 50

Thanks


"bpeltzer" wrote:

Hopefully you checked that if B2 is the max in the row that you indeed get
the format desired. Now you just need to apply the conditional format (CF)
to your entire table. So either select the entire table and apply the CF.
Or select the cell to which you've applied the CF and copy it, then select
your entire table and Edit Paste Special, select the Formats radio button
and click OK.


"Frank Drost" wrote:

I have a list of number, say 10rows by 10 columns. I want to specify a
conditional setting for each cell so that if the cell has the highest value
in its row, the cell will be coloured red. So I will get 10 cells that will
be coloured red. How do I do that?

Thanks