Changing Colour of one cell depending on value of another

[jaarons]

Hi everyone,

I have a question about conditional formatting. I want to change the colour of cell (say B3) if the value is the greatest out of all the cells (not totalled together) in the range of AB3:AB11. Then I want to add the rule to change the cell to another colour if its the second greatest out of the same range and one more if it's the third greatest out of the same range.

Once I've done that, how do I copy the three conditional rules to cells B4 through to B11?

Thanks for your help!

[Melvinrobb]

select B3 - Go to Conditional formatting using a formula and use the following:

Please Login or Register  to view this content.

Create a new conditional format and use this formula

Please Login or Register  to view this content.

Create a new conditional format and use this formula

Please Login or Register  to view this content.

Copy B3 and paste special-formatting on any cells you want this conditional formatting to apply.

[jaarons]

I have done what you suggested (with the minor edit that it was range $AB$3:$AB$11 not from $AB$2) and copy and pasted. It hasn't worked though. I've attached the spreadsheet for you to have a look at.

Many thanks for your help!

[Melvinrobb]

I misinterpreted your request. The attachment helped clarify
change the start of the formula from "=B3=" to "=AB3=" and you should be good to go.

[jaarons]

Thank you so much for your help. I have one more question. I've now converted the sheet to a table, and sorted the teams by colour. Can I make the sort "live" so as I put in numbers that change the totals it will automatically sort with the darker colour on top?

[Melvinrobb]

That is a whole other issue, and probably deserves its own post.
Automatically sorting the list would require a macro.
Alternatively, if you are okay with having two tables: one that you input the data, and one that displays the data, then you should be able to have a second table that changes order accordingly using formulas.