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.

Sum of Amount
Row Labels	Total
Arkansas
Apples	611.00
Bananas	441.00
Oranges	1,607.00
Strawberries	800.00
Arkansas Total	3,459.00
Delaware
Apples	1,506.00
Bananas	450.00
Oranges	950.00
Peaches	200.00
Pears	806.00
Delaware Total	3,912.00
Iowa
Apples	200.00
Bananas	700.00
Oranges	645.00
Peaches	800.00
Pears	230.00
Iowa Total	2,575.00
Grand Total	9,946.00

<tbody>
</tbody>

 

[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

	A​	B​
1​
2​
3​	Rows Labels​	Sum of Amount​
4​	Arkansas ​
5​	Apples​	611,00​
6​	Bananas​	441,00​
7​	Oranges​	1607,00​
8​	Strawberries​	800,00​
9​	Arkansas Total​	3459,00​
10​	Delaware​
11​	Apples​	1506,00​
12​	Bananas​	450,00​
13​	Oranges​	950,00​
14​	Peaches​	200,00​
15​	Pears​	806,00​
16​	Delaware Total​	3912,00​
17​	Iowa​
18​	Apples​	200,00​
19​	Bananas​	700,00​
20​	Oranges​	645,00​
21​	Peaches​	800,00​
22​	Pears​	230,00​
23​	Iowa Total​	2575,00​
24​	Grand Total​	9946,00​

<tbody>
</tbody>

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

	A​	B​
1​
2​
3​	Rows Labels​	Sum of Amount​
4​	Arkansas​
5​	Apples​	611,00​
6​	Bananas​	441,00​
7​	Oranges​	1607,00​
8​	Strawberries​	800,00​
9​	Arkansas Total​	3459,00​
10​	Delaware​
11​	Apples​	1506,00​
12​	Bananas​	450,00​
13​	Oranges​	950,00​
14​	Peaches​	200,00​
15​	Pears​	806,00​
16​	Delaware Total​	3912,00​
17​	Iowa​
18​	Apples​	200,00​
19​	Bananas​	700,00​
20​	Oranges​	645,00​
21​	Peaches​	800,00​
22​	Pears​	230,00​
23​	Iowa Total​	2575,00​
24​	Grand Total​	9946,00​

<tbody>
</tbody>

Hope this helps

M.