Pivot Table conditional formatting using VBA

**Leonprivatemail** · 01-27-2010, 04:56 AM

Hi.

I am extracting data from Access using MicroSoft Query. Due to volume of data, I am taking into a Pivot Table. The table contains a "Targets" field in Row, "Month" field in Column, and has a calculated item in Data.

I need to change the colour & Bold of the values in Data based on whether it is <= 59% of Targets, Between 60% and 79% of Target, Between 80% and 100% of Target.

On top of this, I need to formatting to be applied irrespective of where the Targets fied is in the Pivot, and it has to be applied everytime the pivot is updated.

Att the moment I have it working using conditional formatting, but the moment my "Targets" is moved my formatting disappear.

The pivot is built in Excel 2003.

Thx
Leon

**Andy Pope** · 01-28-2010, 08:58 AM

you could use code in the pivottableupdate event to re apply CF to the appropriate parts of the PT.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

  ' Apply CF code
    
End Sub

Pivot Table conditional formatting using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Pivot Table conditional formatting using VBA

Re: Pivot Table conditional formatting using VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions