Excel Pivot Table Conditional format

Hi, RichardDrake1505

It can barely be done with VBA, my thinking was for example C2 corresponds to A2 with X, then C2 is the starting value, then C6 corresponds to A6 with Y, then C2-C6 is a group, I want the maximum of C2-C6, and so on, but this really can't be achieved with conditional formatting, so VBA was used:

Then I removed the TOTAL as shown:

Then VBA code:

Sub HighlightMaxInGroups() 

    Dim ws As Worksheet 

    Dim lastRow As Long 

    Dim i As Long 

    Dim startRow As Long 

    Dim maxVal As Double 

    Dim maxRow As Long 

    Set ws = ThisWorkbook.Sheets("Sheet2") ' Be sure to use the correct worksheet name 

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 

    startRow = 2 ' Row where data begins 

    For i = startRow To lastRow 

        If ws.Cells(i, 1).Value <> "" Then 

            If i > startRow Then 

                ' Find the maximum value of the current group and highlight it 

                maxVal = Application.WorksheetFunction.Max(ws.Range("C" & startRow & ":C" & i - 1)) 

                maxRow = Application.WorksheetFunction.Match(maxVal, ws.Range("C" & startRow & ":C" & i - 1), 0) + startRow - 1 

                ws.Cells(maxRow, 3).Interior.Color = RGB(255, 255, 0)  

            End If 

            startRow = i 

        End If 

    Next i 

    ' Processing the last group 

    maxVal = Application.WorksheetFunction.Max(ws.Range("C" & startRow & ":C" & lastRow)) 

    maxRow = Application.WorksheetFunction.Match(maxVal, ws.Range("C" & startRow & ":C" & lastRow), 0) + startRow - 1 

    ws.Cells(maxRow, 3).Interior.Color = RGB(255, 255, 0) 

End Sub 

Hold down Alt+F11 to open the VBA editor.

Click Insert-Module and enter the code in the window that opens.

Close all windows.

Hold Alt+F8 to open the Run pane, then select the first one and click Run

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Hi RichardDrake1505,

Thanks for visiting Microsoft Community.

We have tested and discussed this issue together, and unfortunately, the functionality you need is not currently available with conditional formatting. Also, the pivot table itself does not have the ability to highlight specific values in groups.

For the time being, we can only suggest that you manually apply conditional formatting for specific groups. In addition, we will keep this thread open for more good volunteers to discuss with you.

Thank you for sharing your needs here, and if you have any findings, feel free to share them with us here. The Microsoft community is thriving because of you.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist