none
VBA Format a specific Pivot column RRS feed

  • Question

  • Hi 

    I am having difficulty just formatting a specific column called 'Numerator'; I can format all cells that meet the criteria , struggling to just do the one I want , any help appreciated 

    Problem Line: pvt.PivotFields("Numerator").PivotItems.DataRange.Cells

    Private Sub loopPivotTableAllSheet()

        Dim pvt As PivotTable
        Dim sh As Worksheet
        Dim c As Range
        'Loop through all the sheets in a workbook
        For Each sh In ThisWorkbook.Worksheets

            'If there is any pivot table on the sheet
            If sh.PivotTables.Count > 0 Then
        
                'Loop through all the pivots on the sheet
                For Each pvt In sh.PivotTables
                
                For Each c In pvt.PivotFields("Numerator").PivotItems.DataRange.Cells
                ''pvt.DataBodyRange.Cells
                 If c.Value >= 1 And c.Value < 5 Then
            
                 c.NumberFormat = "<5"
     
                
                End If
                Next
               
        Next pvt
                
            End If
        Next sh
        
    End Sub

    Monday, April 6, 2020 2:59 PM

All replies