Adding Colors to Pivot table

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

 

Question Info


Last updated October 9, 2019 Views 6,638 Applies to:
Answer
Answer

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.
If a reply answers your query please click 'Mark it' or click 'Yes' if reply is helpful. Thank you.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.