i have same question , but there no reply , so i copy this topic again

[quanghieuci]

Hello,

Does anybody know how to automatically highlight the highest value in each row label of a pivot table?

In this example, I would like to highlight the highest selling fruit in each state. I couldn't find this option in conditional formatting.

Thanks.



[TABLE="class: cms_table, width: 144"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sum of Amount[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Arkansas[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]611.00[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]441.00[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]1,607.00[/TD]
[/TR]
[TR]
[TD]Strawberries[/TD]
[TD]800.00[/TD]
[/TR]
[TR]
[TD]Arkansas Total[/TD]
[TD]3,459.00[/TD]
[/TR]
[TR]
[TD]Delaware[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]1,506.00[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]950.00[/TD]
[/TR]
[TR]
[TD]Peaches[/TD]
[TD]200.00[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]806.00[/TD]
[/TR]
[TR]
[TD]Delaware Total[/TD]
[TD]3,912.00[/TD]
[/TR]
[TR]
[TD]Iowa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]200.00[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD]700.00[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]645.00[/TD]
[/TR]
[TR]
[TD]Peaches[/TD]
[TD]800.00[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD]230.00[/TD]
[/TR]
[TR]
[TD]Iowa Total[/TD]
[TD]2,575.00[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]9,946.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[ranman256]

see if this macro will work:

Sub aColorizePivot()
Dim vState, vMax
Dim bNew As Boolean
Dim rng As Range


 Range("A4").Select
 bNew = True
While ActiveCell.Value <> "Grand Total"
   If bNew Then
        vState = ActiveCell.Value
        bNew = False
        vMax = 0
   Else
      If InStr(ActiveCell.Value, "Total") > 0 Then
         bNew = True
         'colorize the max value
         rng.Interior.Color = vbYellow   '65535
      Else
         If ActiveCell.Offset(0, 1).Value > vMax Then
            vMax = ActiveCell.Offset(0, 1).Value   'set max value
            Set rng = ActiveCell.Offset(0, 1)
         End If
      End If
      
   End If
   
   ActiveCell.Offset(1, 0).Select  'next row
Wend
  'set final color
rng.Interior.Color = vbYellow   


End Sub

 

[Marcelo Branco]

Or maybe this

Before macro

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Rows Labels​

[/TD]
[TD]

Sum of Amount​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Arkansas
​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Apples​

[/TD]
[TD]

611,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

441,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

1607,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Strawberries​

[/TD]
[TD]

800,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Arkansas Total​

[/TD]
[TD]

3459,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Delaware​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Apples​

[/TD]
[TD]

1506,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

450,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

950,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Peaches​

[/TD]
[TD]

200,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Pears​

[/TD]
[TD]

806,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

Delaware Total​

[/TD]
[TD]

3912,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

Iowa​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

Apples​

[/TD]
[TD]

200,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

700,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

645,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

Peaches​

[/TD]
[TD]

800,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

Pears​

[/TD]
[TD]

230,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

Iowa Total​

[/TD]
[TD]

2575,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

Grand Total​

[/TD]
[TD]

9946,00​

[/TD]
[/TR]
</tbody>[/TABLE]


Macro

Sub aTest()
    Dim pt As PivotTable, pf As PivotField, pi As PivotItem
    Dim rCell As Range, dbMax As Double
    
    Sheets("Sheet1").Activate '<-- adjust sheet name
    Set pt = ActiveSheet.PivotTables("Pivot1") '<--adjust pivot name
    Set pf = pt.PivotFields("State")
    
    For Each pi In pf.PivotItems
        pt.PivotSelect "State[" & pi.Name & "]", xlDataOnly, True
        dbMax = Application.Max(Selection)
        For Each rCell In Selection
            If rCell.Value = dbMax Then rCell.Offset(, -1).Resize(, 2).Interior.Color = vbYellow
        Next rCell
    Next pi
End Sub

After macro

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Rows Labels​

[/TD]
[TD]

Sum of Amount​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Arkansas​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Apples​

[/TD]
[TD]

611,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

441,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD="bgcolor: #FFFF00"]

Oranges​

[/TD]
[TD="bgcolor: #FFFF00"]

1607,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Strawberries​

[/TD]
[TD]

800,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Arkansas Total​

[/TD]
[TD]

3459,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Delaware​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD="bgcolor: #FFFF00"]

Apples​

[/TD]
[TD="bgcolor: #FFFF00"]

1506,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

450,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

950,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Peaches​

[/TD]
[TD]

200,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Pears​

[/TD]
[TD]

806,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

Delaware Total​

[/TD]
[TD]

3912,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

Iowa​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

Apples​

[/TD]
[TD]

200,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

Bananas​

[/TD]
[TD]

700,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

Oranges​

[/TD]
[TD]

645,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD="bgcolor: #FFFF00"]

Peaches​

[/TD]
[TD="bgcolor: #FFFF00"]

800,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

Pears​

[/TD]
[TD]

230,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

Iowa Total​

[/TD]
[TD]

2575,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

Grand Total​

[/TD]
[TD]

9946,00​

[/TD]
[/TR]
</tbody>[/TABLE]


Hope this helps

M.