Pivot table - Colour cell question

[neil40]

Hi

I have a Pivot table that has a compilation of players scores over
several years.

The table shows each players name on the left and in the central area,
his yearly scores.
So, the columns are labelled 1999, 2000, 2001 etc.

In these columns, I would like to highlight (colour) the cell with the
highest score (IE the annual winner)
Is this possible?

Thanks
Neil

 

[Max]

One way via conditional formatting (CF) ..

Assume the "central area" is within B2:E10
Select B2:E10 (with B2 active)
Click Format > Cond Formatting
Under Condition 1, make the settings as
Formula is:
=B2=INDEX(B$2:B$10,MATCH(MAX(B$2:B$10),B$2:B$10,0))
Click Format button > Patterns tab > Green? > OK
Click OK at the main dialog

The CF's format (green fill) should be triggered where the max score
lies within each of the cols' B2:B10, C21:C10, etc (ie the annual
winner). Tied max scores if any, will concurrently trigger the CF
format within the cols. Adapt to suit ..

 

[neil40]

That's excellent Max

Is there any way I can make this 'Future proof' ?
I.E. Next year there will be an extra Column and extra competitors all
the time (rows)

Cheers
Neil

 

[Max]

Perhaps just propagate the CF using the Format Painter? It's a simple
operation, implementable in seconds <g> after the extent of the new /
extended range is "known" when the pivot table is refreshed. With the
CF effected in say B2, just select B2 and double click the Format
Painter icon (the "brush" icon). The cursor will turn into a brush.
Then just "paint" (select) over the required range, eg paint over the
new range say: B2:Z20. Then just press Esc to cancel and revert the
cursor to normal.