none
Visual Basic PivotTable Data Interior Color for RRS feed

  • Question

  •        
           
     
           
           
    EXPANDED
    Resource Manager / Resource  Actuals  ETC  Avail  Total Hrs
    Leader Name 1        
      Resource 1 12.00 10.00 180 12.00
          Elk River Data 12.00 10.00 180 12.00
      Resource 4 6.00 40.00 180 33.00
          Elk River Data 6.00 40.00 180 33.00
       Resource 5 10.00 70.00 180 17.00
    Leader Name 2        
       Resource 2 1.00 33.00 135 14.00
    Leader Name 3 12.00 16.00 180 20.00
    Grand Total 2,562.00 8,726.65 175  11,340.65

    I have 3 Field levels in my Pivot Table that can Expand an Collapse.  In the example, I have Leader Name, Resource, Project Name.  With the below VB code, I was able color the Leader Name level only for Pivot Data Columns Actuals and ETC.  Any cell that was collapsed for the Resource or Project Name was not filled in with the Interior Color set for the column.  I know I can perform an expand all and then apply the interior color, but because of the dynamic nature of the data for the Expand and Collapse of the 3 fields, how can I ensure the color will be filled in completely for the first two columns and leave the other columns alone?

    With Sheets("Sheet1")
        With .PivotTables("PivotTable1")
            With .DataPivotFields
                .PivotItems(1).Caption = " Actuals"
                .PivotItems(2).Caption = " ETC"
                .PivotItems(3).Caption = " Avail"
                .PivotItems(4).Caption = " Total Hrs"
                .PivotItems(1) _
                      .DataRange.Interior.Color = RGB(220, 230, 241)
                .PivotItems(2).DataRange.Interior.Color = RGB(220, 230, 241)

            End With
        End With
    End With

    Monday, January 16, 2017 9:25 PM

All replies

  • Hi Cradata,

    you can try to use "PivotFields" to color the specific fields.

    Example:

    Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("City").PivotItems("London").LabelRange.Interior.Color = vbYellow
    
    

    Reference:

    Referencing an Excel Pivot Table Range using VBA

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 2:49 AM
    Moderator
  • I tried the example your provided (which only covered the label and not the data) and referenced the link provided and setup the following statement, but get the following Run-time error when it tries to execute.

    Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Act Cur Mth") _
       .DataRange.Interior.Color = vbYellow

    Run-time error '1004'

    Unable to get the PivotTables property of the Worksheet class

    Tuesday, January 17, 2017 7:00 PM
  • Okay, I got past he Run-time error listed previously, but when the statement was corrected using the Pivot Field, the VB code below rendered the same result as the initial post.  Only the expanded fields at the time the code ran are shaded.  Any field that was Collapsed did not get any color. 

    Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields(" Actuals") _
       .DataRange.Interior.Color = vbYellow

    I will explain, but I can't insert a picture with color so I will illustrate with words. 

    1) The "Actuals" column in the BEFORE for Leader Name 1, Leader Name 2 and Leader Name 3 does not have any color.

    2) The "Actuals" column in the AFTER picture for Leader Name 1, Leader Name 2 and Leader Name 3 has the color Yellow.

    3) The "Actuals" column in the EXPANDED picture show the color Yellow for Leader Name 1, Leader Name 2 and Leader Name 3, but the Expanded fields Resource 1, Resource 2, Resource 4, Resource 5 and Elk River Data are not colored at all.

    BEFORE
    Resource Manager /Resource  Actuals  ETC  Avail  Total Hrs
    Leader Name 1        
    Leader Name 2        
    Leader Name 3 12.00 16.00 180 20.00
    Grand Total 2,562.00 8,726.65 175 11,340.65
    AFTER
    Resource Manager / Resource  Actuals  ETC  Avail  Total Hrs
    Leader Name 1        
    Leader Name 2        
    Leader Name 3 12.00 16.00 180 20.00
    Grand Total 2,562.00 8,726.65 175 11,340.65
    EXPANDED
    Resource Manager / Resource  Actuals  ETC  Avail  Total Hrs
    Leader Name 1        
    Resource 1 12.00 10.00 180 12.00
    Elk River Data 12.00 10.00 180 12.00
    Resource 4 6.00 40.00 180 33.00
    Elk River Data 6.00 40.00 180 33.00
    Resource 5 10.00 70.00 180 17.00
    Leader Name 2        
    Resource 2 1.00 33.00 135 14.00
    Leader Name 3 12.00 16.00 180 20.00
    Grand Total 2,562.00 8,726.65 175 11,340.65

    Tuesday, January 17, 2017 10:17 PM
  • Hi Cradata,

    I try to test the same on my side.

    I get the same result.

    I will try to find further and if I get something useful then I will provide you.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 18, 2017 5:38 AM
    Moderator