Formatting on Pivot Table View of Form

[mistera]

I have developed a pivot table view of a form in Access 2007 and have two questions regarding formatting.

I'm trying to differentiate two columns of data by color to make it easier to read. However, when I change the background color for one of the columns, it changes the color in both columns. In looking at the attached snippet picture of the pivot table, I would like to change the background color for the value cells in the Budget columns (not totals) to be light blue and in the Actual columns to be light green. How can I do this?

Also, you can see in the picture that some cells show up with 0.00 values while other cells show as blank. How can I force all null/blank cells to show up with a 0.00?

I'm good with having to use VBA to do any of this.

 

[mistera]

Okay, I did some more searching and found code that should work, but for some reason I'm getting a subscript out of range error. Here is the code I added to the form's open event:

    Me.PivotTable.Activeview.DataAxis.FieldSets("Budget").Fields("Budget").DetailBackColor = "Green"
    Me.PivotTable.Activeview.DataAxis.FieldSets("Actual").Fields("Actual").DetailBackColor = "Blue"

I'm not sure what subscript is out of range. I checked that the fieldset and field names are accurate. I also tried using index numbers instead of the names and I get the same error.

Any ideas??