Adding Colors to Pivot table

Question

Hi,

I have a couple of questions:

1. How do you add a columns to a pivot table? I found it the other day and I can't find it again.
2. Within that Pivot table I have a Column name R_Stat that shows G, R, A, H, NS. I want to highlight G with the color Green, R with the color Red, A with the color Amber, and H and NS to stay white.

I was thinking  of adding another column and placing a conditional if statement with the colors on a seperate worksheet. Is there a way to highlight them within VBA? I am a beginner of VBA for Excel so bare with me.

Thanks!

Sarita

Answer 1

Glad it worked... I have reiterated the code with the changes required for the RAG cells but if it is reuiqred for the H and NS cells just copy the relevant lines as required and amend.

Private Sub Worksheet_Calculate()

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'R'", _

        xlDataAndLabel, True

Selection.Font.Bold = True     Selection.HorizontalAlignment = xlCenter

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'A'", _

        xlDataAndLabel, True

Selection.Font.Bold = True     Selection.HorizontalAlignment = xlCenter

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 33023

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'G'", _

        xlDataAndLabel, True

Selection.Font.Bold = True     Selection.HorizontalAlignment = xlCenter    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 65280

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

Best of luck!

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 2

oops copy paste error sorry for that... no need for macro line. Please try again with code below...

Private Sub Worksheet_Calculate()

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'R'", _

        xlDataAndLabel, True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'A'", _

        xlDataAndLabel, True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 33023

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'G'", _

        xlDataAndLabel, True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 65280

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 3

Hi,

The conditional formatting works but I wanted the embedded code in the back-end. I am still learning it for excel so bare with me. This part isn't working

"This is hightlighted" Private Sub Worksheet_Calculate() I changed the ActiveSheet name but when I step into this to debug it this is what I get Compile Error: Expected End Sub. I know its something simpe. Also was there a reason why you created it as a macro? Do I have to create a macro for every VBA code I write?

Sarita

Answer 4

With regard to point 2 - and assuming that the cells to color are on the left hand side of the pivot table data

Right click the tab of the sheet with the pivot table in it and select View Code, this opens the editor at the right place. Copy and paste this code into the editor page. Close the vba Editor. Now every time the worksheet is calculated the R A G cells will be highlighted in the colors.

Private Sub Worksheet_Calculate()

Sub Macro1()

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'R'", _

        xlDataAndLabel, True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'A'", _

        xlDataAndLabel, True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 33023

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'G'", _

        xlDataAndLabel, True

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 65280

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

End Sub

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 5

Take a look at Debra Dalgleish's page

http://www.contextures.com/tiptech.html

go down to the C entries and look at conditional formatting (click on the link).  Start with the Basics Topic.

---

--

Tom Ogilvy

note: If you receive an answer to your question - please mark that answer or answers so others know the question has been answered.