Keep Pivot Table Source Data Formatting

[Lorlai]

Is there a way to maintain the formatting I have done to a pivot table's source data? For instance, if I highlight a field in the source data, is there a way to keep this highlight through any pivot tables that are created?

Thank you!

 

[wigi]

No, but you can format the pivot table and reserve that formatting through different updates / refresh actions.

 

[Lorlai]

Thanks for the response Wigi. Do you know if there is a way to look at the source data, and if there is a change, to create some code to highlight the change?

I have a workbook that contains a sheet of source data and then about 10 sheets of pivot tables based on this source data. This workbook is updated everyday. I need to find a way to look at this data once a week and quickly see the changes. My idea is to compare the source data row by row and cell by cell. If a row has been deleted or added, I want to insert a blank row in the corresponding workbook. If a cell has a different value, I want to highlight that cell. With the new information you were able to tell me, I am wondering now if I can take the source data's highlighted cell and write some form of code that will say if sourcedata.cell = highlighted, then in when this cell appears in the pivot table, highlight it as well.

Do you know if this is possible?

Thank you!

 

[wigi]

That will require quite some code... I am really not sure if it's all worth it.

Look at Worksheet_Change events, that way you can color the cell or cells that changed (Target.Interior.ColorIndex = 3 for instance, then you put the cells in red background color).

 

[Lorlai]

Thanks again Wigi! One more question, if you don't mind:

I have the two workbooks that I am comparing, and I have a third workbook to record these changes. This third workbook contains the macro to run the whole process.

What I've been reading on the worksheet_change events says that you have to physically select the worksheet you want to record the changes. If this will need to be done from a macro, how can I record the worksheet_changes on an unknown workbook sheet? (the workbooks have different names, identifying the date in the title).

Thank you so much for your masterful help!!

 

[wigi]

Sorry, but I do not understand this.

A Worksheet_Change event macro will be placed in the code module of the sheet where the input / changes are done (hence the name Worksheet_Change).

What do you mean with "physically select the worksheet you want to record the changes"? It's the sheet / cells itself that will get coloured.

What is the "unknown workbook sheet"?

 

[Lorlai]

I'm sorry, I am very new to vba and am having a hard time wrapping my head around some of the concepts.

I had looked at this forum and jumped to the conclusion that you had to follow those steps one by one.

As a clarification, do you mean it is possible to do:

loop through cells in both sheets
cell x is different
highlight cell x
worksheet_change event
(not sure exactly what would need to be done from this point to color all of the cells that had been changed)
continue looping through cells

Is that somewhat right, or am I totally confused?

 

[wigi]

You can loop through cells and color them, but then you do not need that Worksheet_Change event. Since you color a cell, there is no change in value in a cell, so the event won't even fire. But within the loop and colouring, you could do whatever is needed to make the output on differences in a third sheet, for instance.

 

[Hermanito]

Instead of the Worksheet_Change event, you might use this event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    'your code to format the sourcedata comes here
End Sub

 

[Lorlai]

Thank you both for your responses. I'm afraid I don't understand how the process really works, however.

If I were to make something like:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Does adding it in here mean that all occurences of that data will appear in all pivot tables?

Sub Test()

Dim CellValue As String
Dim c As Range

Sheets("Previous").Select
Range("A1").Select
Sheets("Test11").Select
Range("A1").Select


Do

Sheets("Previous").Select
CellValue = ActiveCell.Value

Sheets("Test11").Select
    If ActiveCell.Value <> CellValue Then
    Worksheet_PivotTableUpdate
    End If
ActiveCell.Offset(0, 1).Select

Sheets("Previous").Select
ActiveCell.Offset(0, 1).Select


Loop Until IsEmpty(ActiveCell)

End Sub

Thank you for the suggestions!