How can I make my conditional formatting macro format the correct columns in a pivot table?

monuw

New Member
Joined
May 3, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!

Desired Result:
1651615440402.png

Actual Result
1651615476988.png

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.

VBA Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,175,866
Messages
5,899,935
Members
434,806
Latest member
jianroy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top