'How can I make my conditional formatting macro format the correct columns in a pivot table?
I have a pivot table where the rows contain some categories and the columns contain dates in ascending order and the values in the table are $ amounts. I am running a macro which puts the same 'amount' field in the 'values' box again and then I use 'show as difference from' to see the changes in the amounts b/w different dates. The final step in this process is to conditionally format those changes to give +ve changes green fill and -ve changes a red fill. However, when I run the macro, it formats the original amounts columns and not the 'difference' columns.
I am attaching the pictures and my vba scripts. I would appreciate any help. TIA!
After creating the difference columns, this is what the code looks like. 'Sum of Daily AUM' is the name of the original columns. The difference columns are called 'change'. But if I replace 'Sum of Daily AUM (Cr.)' with 'Change' in the 2nd line, it throws an error saying it doesn't recognize that object.
ActiveSheet.PivotTables("PivotTable9").PivotSelect _
"Values['[Measures].[Sum of Daily AUM (Cr.)]']", xlDataAndLabel, True
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|


