Format / Highlight top 3 values at row level

[kr3geyn]

Hi,

I designed a pivot table and have the datas in percentage. So for each row, I have to highlight top 3 values (highest by individual row level) in different colors. Can someone help me with it?

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]SUBJECT
[/TD]
[TD]MARK1
[/TD]
[TD]MARK2
[/TD]
[TD]MARK3
[/TD]
[TD]MARK4
[/TD]
[/TR]
[TR]
[TD]SUB1
[/TD]
[TD]86
[/TD]
[TD]67
[/TD]
[TD]12
[/TD]
[TD]76
[/TD]
[/TR]
[TR]
[TD]SUB2
[/TD]
[TD]45
[/TD]
[TD]56
[/TD]
[TD]35
[/TD]
[TD]97
[/TD]
[/TR]
[TR]
[TD]SUB3
[/TD]
[TD]49
[/TD]
[TD]34
[/TD]
[TD]13
[/TD]
[TD]94
[/TD]
[/TR]
[TR]
[TD]SUB4
[/TD]
[TD]46
[/TD]
[TD]78
[/TD]
[TD]73
[/TD]
[TD]95
[/TD]
[/TR]
[TR]
[TD]SUB5
[/TD]
[TD]94
[/TD]
[TD]39
[/TD]
[TD]23
[/TD]
[TD]94
[/TD]
[/TR]
</tbody>[/TABLE]

Take for example this sample table. For sub 1, the highest values are MARK1, MARK4 and MARK2

I want only these cells to be highlighted. Similarly for SUB2, SUB3 and so on.

I will be very much happy if it can be done in different colors

 

[Weazel]

Assuming your table starts in A1 and the first value is in b2....

highlight the table of values then create a new conditional formatting rule with

=B2>=LARGE($B2:$E2,3)

Essentially this will highlight the 3 largest values in the row.
you could do create the same formula for each row individually if you wanted different colors per row
but if you want different colors in each row you'd need to create seperate formulas for each color....

kind of time intensive I think unless someone has another suggestion maybe VBA.

 

[markmzz]

What are the highest values for SUB5 and SUB6?

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]SUBJECT
[/TD]
[TD]MARK1
[/TD]
[TD]MARK2
[/TD]
[TD]MARK3
[/TD]
[TD]MARK4
[/TD]
[/TR]
[TR]
[TD]SUB5
[/TD]
[TD]94
[/TD]
[TD]39
[/TD]
[TD]23
[/TD]
[TD]94
[/TD]
[/TR]
[TR]
[TD]SUB6
[/TD]
[TD]23
[/TD]
[TD]23
[/TD]
[TD]50
[/TD]
[TD]70
[/TD]
[/TR]

</tbody>[/TABLE]

Markmzz