Rule that colours highest value and lowest value

[Harwizzywood]

Is there any way I can get excel to detect which cells in each row are higher and lower values and colour them automatically?

im trying to do a spreadsheet that makes it easier for the company I work at to see which of our suppliers have the best prices on the same product but as prices change I don’t want to have to keep changing the colours manually I hope I would be able to change the new price and the rule detects it and colours the highest value red and lowest green.

eg. D6 is £1(green) and F6 is £2(red) but next week I change D6 to £2(red) and F6 is £1(green)

[#2 · Accepted solution]

@Harwizzywood 

Let's say you have prices in C2:K100.

Select this range. I will assume that C2 is the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=C2=MIN($C2:$K2)

Click Format...
Activate the Fill tab.
Select green as highlight color.
Click OK, then click OK again.

Repeat these steps, but with the formula =C2=MAX($C2:$K2) and red as color.