• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1356
  • Last Modified:

Referencing Pivot Table Ranges using VBA in Excel 2010 to Change Cell and Font Colours

Hello,

I have a Pivot Table that currently changes the colour of items marked "1" for different categories. For example, fields within HIGH become red, MEDIUM is orange, and LOW is yellow.

This worked great when I only had three "row label" items, but now I wish to add a fourth containing additional details within the Pivot table. The result is that only certain rows have their colour changed, yet I want the remaining 1's font colour turned white so they don't show.  

A before and after screenshot below explains what I mean.
Before:
beforeAfter adding the detail row label:
afterIf the font colour of those remaining number 1's were changed to white so they don't show it would look like this:
end resultOnce this has been applied I will be changing the layout back to "Compact Form", where I know it keeps the changes made to font colour:
compact viewIt needs to be done programmatically using VBA because there may be more rows and columns depending on the source data.

I've attached the Macro enabled spreadsheet here:
PivotSelect.xlsm

Many thanks in advance,
Paul.
picb4.png
0
jarrah10
Asked:
jarrah10
  • 5
  • 4
1 Solution
 
krishnakrkcCommented:
Hi

Add these two lines at the end of the code

pvtPivot.PivotSelect "Category2[All;Total]", xlDataOnly, True
Selection.Font.Color = Selection.Interior.Color

Open in new window


Kris
0
 
jarrah10Author Commented:
Hi Kris,

So close! I was amazed that you were able to help on this as I just didn't think it was possible, however there's one problem that is very odd.

Applying your code works a treat to make those's 1's turn white when in Tabular Form, but when I change the layout back to Compact Form, all those's 1's become visible again!

However if I manually select the exact same cells that the code selects and manually change the font colour to white, the colour is preserved when going to Compact mode and the 1's are still invisible...

I can't see why the manual method would work when the code doesn't because there appears to be no difference, very odd indeed.

Any thoughts?
0
 
krishnakrkcCommented:
Hi,

Put that code in different sub and call after changing the layout. Didn't try myself, try it and let me know how it goes.

Kris
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jarrah10Author Commented:
Thanks for replying.

Unfortunately the "Total" text disapears in Compact Form so the code can't search for those particular rows. (the 1's are missing here because I resued the image from above)
compact formAlso in my main Pivot tables there's no common wording within the field that can be used in compact form either. (i.e the word Category2 A/B/C is unique for each row).
0
 
krishnakrkcCommented:
Hi

Put this in Sheet (Pivot) module

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim pvtPivot    As PivotTable
    Dim pvtField    As PivotField
    
    Set pvtPivot = Worksheets("Pivot").PivotTables("PivotTable2")
    Set pvtField = pvtPivot.PivotFields("Level")
    
    If pvtField.LayoutForm = xlOutline Then
        pvtPivot.PivotSelect "Category2[All;Total]", xlDataOnly, True
        Selection.Font.Color = Selection.Interior.Color
    End If

End Sub

Open in new window


Kris
0
 
jarrah10Author Commented:
Hi Kris,

Took me a moment to figure out how it should work because as you can probably already tell I'm a VB beginner. :)

So by putting the private sub in the Pivot module, it executes the sub whenever it detects changes to the pivot table right? It then looks for when the layout form becomes outline and attempts to make the required changes. In this case, even if I choose Compact (from the list of Compact, Outline, and Tabular), it must somehow be partially "Outline" because the code does try to run.

However, it unfortunately errors when I choose either Compact or Outline by saying:
"Run-time error '1004': The formula is not complete. Make sure an ending square bracket ] is not missing". Is this because it can't find "Total" within the rows?

If I change the If statement to say "xlTabular" instead of "xlOutline" then the code does run fine, making those 1's turn white whenever I choose Tabular Form, but goes back to showing 1's in the other two forms.

It's bugging me that manually changing the font colour works, yet it doesn't seem logical that the code fails to preserve the change!

I must admit it's not the direction I want to go in anyway because my main code sits in a seperate spreadsheet to the source data. The Macro runs against the source data to create a new sheet where it then inserts a new Pivot Table, so it would be impossible to use the private sub in my case.
0
 
krishnakrkcCommented:
Hi

Since the pivot doesn't preserve the format, I think a cahnge event code will be a better option. If you can't go with that one, I'm sorry I can't help you further.

In the meantime have you ticked the 'Preserve cell formatting on update' via Pivot table options ?

If you still want to go with the change event, you can use case statement to customise.

see if this helps

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim pvtPivot    As PivotTable
    Dim pvtField    As PivotField
    
    Set pvtPivot = Worksheets("Pivot").PivotTables("PivotTable2")
    Set pvtField = pvtPivot.PivotFields("Level")
    
    Select Case pvtField.LayoutForm
        Case xlOutline
            pvtPivot.PivotSelect "Category2[All;Total]", xlDataOnly, True
            Selection.Font.Color = Selection.Interior.Color
        Case xlTabular
            'do whatever
    End Select

End Sub

Open in new window


Kris
0
 
jarrah10Author Commented:
Hi Kris,

Thanks for your help.

I'm finding that the change event code only works in tabular mode because if I select Outline or Compact I receive the same error message as mentioned above, yet your code seems to imply it should work in Outline mode? Is this the same for you or does it remove the 1's in Outline ok?

Yes the preserve cell formatting is selected.

Sooo close to the solution - shall I start a new question to solve this particular issue because you did answer my initial query and I want to award you for the help?
0
 
krishnakrkcCommented:
shall I start a new question to solve this particular issue because you did answer my initial query

That would be better.

Kris
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now