none
VBA to change font color of subtotal rows RRS feed

  • Question

  • Good day,
    Does anyone know how to change the font color of the subtotal rows using VBA?

    Thanks!

    Casey

    Tuesday, September 1, 2015 7:12 PM

Answers

  • Hi Casey,

     

    Based on my undersanding, we can use PivotTable.PivotSelectto select part of a PivotTable report. And here is an sample to select the sepcific Pivot Field:

     ActiveSheet.PivotTables("PivotTable1").PivotSelect "Categories", xlLabelOnly + _
            xlFirstRow, True
            
            With Selection.Font
            .Color = -16776961
            .TintAndShade = 0
        End With

    Here is the figure for the data and PivotTable:

    In addition, we can use Record Macro to get quick start for a sample code.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 2, 2015 7:02 AM
    Moderator

All replies

  • Here's what I'm trying to do:

    On the attached image, I'd like to  modify the color and make bold B through D, on rows 6, 12, and 15.  However, since the rows are subject to change based on the underlying data, I need to do it programmatically, either with some vba command that applies to subtotals only, or by looping through the pivot table, and wherever the pivot field named "participant" (and wherever that field occurs, take the row and use that to modify color/font on "B-row:D-row")

    Tuesday, September 1, 2015 8:15 PM
  • Hi Casey,

     

    Based on my undersanding, we can use PivotTable.PivotSelectto select part of a PivotTable report. And here is an sample to select the sepcific Pivot Field:

     ActiveSheet.PivotTables("PivotTable1").PivotSelect "Categories", xlLabelOnly + _
            xlFirstRow, True
            
            With Selection.Font
            .Color = -16776961
            .TintAndShade = 0
        End With

    Here is the figure for the data and PivotTable:

    In addition, we can use Record Macro to get quick start for a sample code.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 2, 2015 7:02 AM
    Moderator