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

Posted on 2012-08-24
Last Modified: 2012-08-29
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)

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.
Many thanks in advance,
Question by:jarrah10
    LVL 18

    Accepted Solution


    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
        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
    End Sub

    Open in new window

    LVL 1

    Author Comment

    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.

    LVL 1

    Author Comment

    Got it:

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

    Thanks very much for your help!!!
    LVL 18

    Expert Comment


    Glad you sorted out yourself. :)

    LVL 1

    Author Comment

    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?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now