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

Referencing Pivot Table Ranges in VBA in Excel 2010 for Conditional Formatting

Hello,
I would like to know how select a range of cells (using VBA) within a Pivot Table that belong to a specific category please. (as seen in the screenshot below)
pic1My aim is to programmatically give those cells with a "1" a different colour based on the level of importance. For example HIGH will be red, and MEDIUM will be orange. This pic shows how I want the end result to look like:
pic2The reason why this needs to be done in VBA is because I create many pivot tables based on the same categories but with different data. There could be many more rows within HIGH or a different number of columns based on the data source, so it needs a PivotTable VBA command that knows the chart's boundaries so that the relevant conditional formatting can be applied.

What I have figured out so far is the ability to select cells based on their category using the VBA code below, but the issue is it selects category totals rather than all cells within a category (screenshot below shows what the code actually selects, which are the cells to the right of HIGH)
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.PivotFields("Level").PivotItems("HIGH").DataRange.Select

Open in new window

pic3I'm hoping someone on here has the technical know-how to help solve this please?
Attached is a temporary .xlsm which has my raw data (test values for this purpose), the pivot table, and the VBA code which does the selection.
PivotSelect.xlsm
Many thanks in advance,
Paul.
0
jarrah10
Asked:
jarrah10
  • 3
  • 2
1 Solution
 
krishnakrkcCommented:
Hi

Try this

Sub kTest()
    
    Dim pvtPivot    As PivotTable
    Dim pvtField    As PivotField
    Dim pvtItem     As PivotItem
    
    Set pvtPivot = Worksheets("Pivot").PivotTables("PivotTable2")   'Adjust to suit
    
    For Each pvtField In pvtPivot.RowFields
        If Not pvtField.LayoutForm = xlTabular Then pvtField.LayoutForm = xlTabular
    Next
    
    Set pvtField = pvtPivot.PivotFields("Level")           'Adjust to suit
    
    For Each pvtItem In pvtField.PivotItems
        pvtItem.DataRange.Interior.Color = -4142
        Select Case UCase$(pvtItem.Name)
            Case "BEST"
                pvtItem.DataRange.SpecialCells(2, 1).Interior.Color = 65280
            Case "HIGH"
                pvtItem.DataRange.SpecialCells(2, 1).Interior.Color = 255
            Case "LOW"
                pvtItem.DataRange.SpecialCells(2, 1).Interior.Color = 65535
            Case "MEDIUM"
                pvtItem.DataRange.SpecialCells(2, 1).Interior.Color = 49407
        End Select
    Next

End Sub

Open in new window


Kris
0
 
jarrah10Author Commented:
Hi Kris,

Thank you very much, your code does exactly what I was after! I see what you've done in that for it to detect the correct BEST/HIGH, etc field you had to change the layout to Tabular so that the correct cells could be highlighted and modified. Great idea, never thought of that!!

To turn the layout back to compact view I know I can just add the following code:

ActiveSheet.PivotTables("PivotTable2").RowAxisLayout xlCompactRow

However, one quick question please before I mark it as a definate answer, what is the code to change the font colour as well please? I would like the cell to be purely the colour needed so the value "1" needs to match the background colour as well please.

Thanks,
Paul.
0
 
jarrah10Author Commented:
Got it:

pvtItem.DataRange.SpecialCells(2, 1).Font.Color = 65280

Thanks very much for your help!!!
0
 
krishnakrkcCommented:
Hi

Glad you sorted out yourself. :)

Kris
0
 
jarrah10Author Commented:
Hi Kris,

I have one more question for a very similar issue using the same Pivot table that I'm hoping you may be able to help with please?

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27846143.html
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now