Relative conditional formatting for highest and lowest values

[raj_srini]

I have a spreadsheet showing the following

[TABLE="width: 336"]
<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Item No.[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Vendor 1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Vendor 1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Vendor 2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Vendor 2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Vendor 3[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Vendor 3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]Rate[/TD]
[TD="class: xl65, bgcolor: transparent"]Amt[/TD]
[TD="class: xl65, bgcolor: transparent"]Rate[/TD]
[TD="class: xl65, bgcolor: transparent"]Amt[/TD]
[TD="class: xl65, bgcolor: transparent"]Rate[/TD]
[TD="class: xl65, bgcolor: transparent"]Amt[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

For each item, I want to highlight the lowest rate with green and highest rate with red. I tried various permutations with conditional formatting, but I have not been able to get the same to apply with relative reference across various rows with columns spaced out.

Any help would be very much appreciated.

 

[jai9]

Hi,

Try this.

Select the range for Vendor1 Rate.
Conditional Formatting > New Rule > Format only Top or Bottom ranked Values
Select 1 % for top as well as bottom.
Format as required.
Repeat the same for other ranges.

Jai

 

[raj_srini]

Dear Jai9,

Thanks for taking the time to respond.

I am sorry. I should have been clearer. I'm actually looking for top and bottom ranked values by item, not by vendor.

I'm doing a rate comparison. So if vendor 1 has the rate for item 1 as 20, vendor 2 as 30, vendor 3 as 12 - I want vendor 3 rate to be highlighted in green ( being the lowest) and vendor 2 rate to be highlighted in red ( being the highest).

Thanks for helping !

 

[jai9]

Hi,

I put the data in A1:G12.

Try this.
Select the cell B3 which is for Vendor1 Rate.
Press control and select cells D3 and F3 which are for vendor 2 rate and vendor 3 rate.

Apply the same conditional formatting as I told in the previous post.

This would be for Item 1.

Select the row and use format painter to apply same format for the rows below.

Jai