How do I loop through source data to update font attribs in Pivot Tables?

[rmillar]

Hi All,

Hope one of you can help me out...

I have an Excel 2007 workbook that has some pivot tables in it and as we are all aware, the formatting from the source data does not carry over into the pivot table and even if you manage to reformat the data in the pivot table it gets whacked every time the pivot table is changes...

So, in my workbook, I have a macro that goes through and updates my pivot table data format when invoked. It works fine. However, I also want to be able to update the font colors of the data values in the pivot table based on the font color of the underlying source data. Can any of you throw me a fish on how I can loop through the source data and the pivot table data values to do this?

My current macro is below as a reference.

Public Sub format_zero_valued_cells()
Dim rngPTData As Range
Dim RngCell As Range
Dim RngSrcData As Range
 
Set rngPTData = ActiveSheet.PivotTables(1).DataBodyRange
     'set start cell for find
Set RngCell = rngPTData.Cells(1)
Application.ScreenUpdating = False
For Each RngCell In rngPTData
  If RngCell.Value = 0 Then
    RngCell.Font.ColorIndex = 37
  End If
Next RngCell
Application.ScreenUpdating = True
Set rngPTData = Nothing
Set RngCell = Nothing
End Sub

I also took a look at some code written by Mike Alexander at datapig that is pretty cool and gets me part way there, but it only takes me to the column level and I havn't been able to figure out how to modify it to my needs. See http://datapigtechnologies.com/blog...tch-source-data/comment-page-1/#comment-20916

Any insight will be greatly appreciated.

thanks,

rick

 

[Sal Paradise]

This line is changing the font colors:

  If RngCell.Value = 0 Then
    RngCell.Font.ColorIndex = 37
  End If

If you set more cases, with different value ranges, and different colors, it will color them too. You may want to use a SELECT CASE rather than a bunch of Ifs.

The loop that the above code is in goes through every cell in your pivot body data.

 

[rmillar]

Sal, Thanks for responding. I'm sorry, I must not have been clear in my request. I already understand what the code I posted does. What I would like is help enhancing it to update the font color of other (non-zero-valued) cells in the data range of the pivot table using the font colors of the underlying source data. I imagine I need to loop through both the source data and the pivot table data simultaneously to pull the source data format and apply it to the pivot data. Just not sure how to do that.