PivotTableFlash3

In my last PivotTable post, I showed how to hide PivotTable Field Captions. However, that hides the Filter Arrows as well.

PTNoFieldCaptions

What if you want to hide the Field Captions, but display the filter arrows?

PTArrowsNoLabels

I change the font color of the Field Captions to match the Range Interior ColorIndex

Sub PTFieldCaptionsChangeFontColor()

    'Declare variables
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim rng As Range
        Dim lngRangeColor As Long

    'Excel environment - speed things up
        Application.ScreenUpdating = False

    'Initialize variables
        Set wb = ThisWorkbook

    'Loop all PivotTables in all worksheets in the workbook
    'Set Font Color of Field Captions to same color as Cell Interior ColorIndex
        For Each ws In wb.Worksheets
            For Each pt In ws.PivotTables
                
                'Get the interior fill color of the Column Range of the Pivot Table
                    Set rng = pt.ColumnRange
                    lngRangeColor = GetRangeColor(rng:=rng)
                    
                'Set the Font Color of the Field Captions to the same color as the Range Interior Color
                    Call ChangeFontColor(rng:=rng, _
                                         lngColor:=lngRangeColor)
    
            Next pt
        Next ws

    'Tidy up
        'Destroy objects
            Set rng = Nothing
            Set wb = Nothing

        'Restore Excel environment
            Application.ScreenUpdating = True

End Sub
'-----------------------------------------------------------------
Private Function GetRangeColor(rng As Range) As Long

    Dim lngColor As Long
    lngColor = rng.Interior.ColorIndex
    GetRangeColor = lngColor
    
End Function
'-----------------------------------------------------------------
Private Sub ChangeFontColor(rng As Range, _
                            lngColor As Long)
                            
    Dim rngRow As Range
    Dim rngColumn As Range
    Dim rngBig As Range

    Set rngRow = rng.Offset(1, -1).Resize(rng.Rows.Count - 1, 1)
    Set rngColumn = rng.Resize(rng.Rows.Count - 1, 1)
    Set rngBig = Union(rngRow, rngColumn)
    
    rngBig.Font.Color = lngColor
    
    Set rngBig = Nothing
    Set rngColumn = Nothing
    Set rngRow = Nothing

End Sub

PTFieldCaptionsInvisible

Other PivotTable Posts At dataprose.org

  1. PivotTable Hide Field Captions
  2. PivotTable Conditional Formatting
  3. PivotTable Cell Borders

Additional Resources

  1. Contextures
  2. Peltier Technical Services, Inc.
  3. Chandoo
, ,
Trackback

only 1 comment untill now

  1. […] my last post, I showed how to hide the Field Captions of a Pivot Table by changing the font color to match the […]

Add your comment now