I want to color the cells of a pivot table in a way that it highlights the highest values in the row. The table I have is something like this:
________|_Field1_|_Field2_|
________|_X_______________|_X_Total_|_Y________________________|_Y_Total_|
_Field3_|____A___|____B___| |____A___|____C___|____D___| |
____1___|____3___|____4___|____7____|____3___|____4___|____7___|____14___|
____2___|____7___|____4___|____11___|____1___|____8___|____3___|____12___|
.
.
Basically it's the values of Field1 and 2 against those of Field3. So for Field1 value of X Field2 values of A and B are shown against '1' from Field3.
What I'm struggling with is to color the values for Field3 value '1' where the highest value is colored Red, 2nd highest blue and 3rd highest green. So for example in row '1', for Field1 value 'X' B(4) becomes red and A(3) becomes blue, and in the same row for Field1 value 'Y' D(7) becomes red, then C(4) becomes blue and A(3) is green. Likewise in row '2', X: A(7) is red, B(4) green, Y: C(8) is red and so on.
This table is randomly generated so the number of columns per Field1 value can change (X,Y etc.). I found formatting the rows through the available options takes in to account the values in the entire row (even totals) so that's a no-go. Can this be done? Thanks in advance!