Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Pivot Row Totals
Hi,
I'm trying to write a macro to highlight the total rows in my pivot tables. I have Office 03. Does anyone know how I can determine what colums / rows the pivot table occupies to so that I can apply the correct formatting? I have some code that doesn't work too well (posted at the bottom) The logic of the program I want to write is: Get the range that the pivot table occupies (e.g. A3:F100) (referred to below as Pivot Range) For each cell in the Pivot Range (assuming that the loop will go a3, b3, c3, d3....) if the cell contains the word Total then Format the row from the current cell to the last column of the pivot table range a certain color go to the next row The only other thing worth noting is that I only want to highlight from the column with the total to the end of the pivot, so if c3 contains a total then in the example above, c3 - f3 would be highlighted. My code so far: Sub Help() Dim pt As PivotTable Set pt = ActiveCell.PivotTable pt.TableRange2.Select 'this selects the range that the pivot occupies For Each Cell In Selection Set x = Cell.Find("Total") If x = "" Then x = "" Else With Cell.Interior .ColorIndex = 26 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next Exit Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Pivot Row Totals
You could use code similar to the following, to colour the row field
totals and the grand total. '=============== Sub ColourRowTotals() Dim pt As PivotTable Dim pf As PivotField Dim iColour As Integer iColour = 35 'light green Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.RowFields On Error Resume Next pt.PivotSelect pf.Name & "[All;Total]", xlDataAndLabel, True With Selection.Interior .ColorIndex = iColour .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Next pf pt.PivotSelect "'Column Grand Total'", xlDataAndLabel, True Selection.Interior.ColorIndex = iColour End Sub '=============== Tim879 wrote: Hi, I'm trying to write a macro to highlight the total rows in my pivot tables. I have Office 03. Does anyone know how I can determine what colums / rows the pivot table occupies to so that I can apply the correct formatting? I have some code that doesn't work too well (posted at the bottom) The logic of the program I want to write is: Get the range that the pivot table occupies (e.g. A3:F100) (referred to below as Pivot Range) For each cell in the Pivot Range (assuming that the loop will go a3, b3, c3, d3....) if the cell contains the word Total then Format the row from the current cell to the last column of the pivot table range a certain color go to the next row The only other thing worth noting is that I only want to highlight from the column with the total to the end of the pivot, so if c3 contains a total then in the example above, c3 - f3 would be highlighted. My code so far: Sub Help() Dim pt As PivotTable Set pt = ActiveCell.PivotTable pt.TableRange2.Select 'this selects the range that the pivot occupies For Each Cell In Selection Set x = Cell.Find("Total") If x = "" Then x = "" Else With Cell.Interior .ColorIndex = 26 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next Exit Sub -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Pivot Row Totals
This worked like a charm. I've spent so much time on it and was
getting so frustrated that I could not solve it. thank you so much for your help. On Nov 9, 8:58 pm, Debra Dalgleish wrote: You could use code similar to the following, to colour the row field totals and the grand total. '=============== Sub ColourRowTotals() Dim pt As PivotTable Dim pf As PivotField Dim iColour As Integer iColour = 35 'light green Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.RowFields On Error Resume Next pt.PivotSelect pf.Name & "[All;Total]", xlDataAndLabel, True With Selection.Interior .ColorIndex = iColour .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Next pf pt.PivotSelect "'Column Grand Total'", xlDataAndLabel, True Selection.Interior.ColorIndex = iColour End Sub '=============== Tim879 wrote: Hi, I'm trying to write a macro to highlight the total rows in my pivot tables. I have Office 03. Does anyone know how I can determine what colums / rows the pivot table occupies to so that I can apply the correct formatting? I have some code that doesn't work too well (posted at the bottom) The logic of the program I want to write is: Get the range that the pivot table occupies (e.g. A3:F100) (referred to below as Pivot Range) For each cell in the Pivot Range (assuming that the loop will go a3, b3, c3, d3....) if the cell contains the word Total then Format the row from the current cell to the last column of the pivot table range a certain color go to the next row The only other thing worth noting is that I only want to highlight from the column with the total to the end of the pivot, so if c3 contains a total then in the example above, c3 - f3 would be highlighted. My code so far: Sub Help() Dim pt As PivotTable Set pt = ActiveCell.PivotTable pt.TableRange2.Select 'this selects the range that the pivot occupies For Each Cell In Selection Set x = Cell.Find("Total") If x = "" Then x = "" Else With Cell.Interior .ColorIndex = 26 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next Exit Sub -- Debra Dalgleish Contextureshttp://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight Pivot Row Totals
You're welcome, and thanks for letting me know that it worked. It helped
that you clearly explained what you were trying to do, and showed the code that you'd written. Tim879 wrote: This worked like a charm. I've spent so much time on it and was getting so frustrated that I could not solve it. thank you so much for your help. On Nov 9, 8:58 pm, Debra Dalgleish wrote: You could use code similar to the following, to colour the row field totals and the grand total. '=============== Sub ColourRowTotals() Dim pt As PivotTable Dim pf As PivotField Dim iColour As Integer iColour = 35 'light green Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.RowFields On Error Resume Next pt.PivotSelect pf.Name & "[All;Total]", xlDataAndLabel, True With Selection.Interior .ColorIndex = iColour .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Next pf pt.PivotSelect "'Column Grand Total'", xlDataAndLabel, True Selection.Interior.ColorIndex = iColour End Sub '=============== Tim879 wrote: Hi, I'm trying to write a macro to highlight the total rows in my pivot tables. I have Office 03. Does anyone know how I can determine what colums / rows the pivot table occupies to so that I can apply the correct formatting? I have some code that doesn't work too well (posted at the bottom) The logic of the program I want to write is: Get the range that the pivot table occupies (e.g. A3:F100) (referred to below as Pivot Range) For each cell in the Pivot Range (assuming that the loop will go a3, b3, c3, d3....) if the cell contains the word Total then Format the row from the current cell to the last column of the pivot table range a certain color go to the next row The only other thing worth noting is that I only want to highlight from the column with the total to the end of the pivot, so if c3 contains a total then in the example above, c3 - f3 would be highlighted. My code so far: Sub Help() Dim pt As PivotTable Set pt = ActiveCell.PivotTable pt.TableRange2.Select 'this selects the range that the pivot occupies For Each Cell In Selection Set x = Cell.Find("Total") If x = "" Then x = "" Else With Cell.Interior .ColorIndex = 26 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next Exit Sub -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table % of Totals | Excel Discussion (Misc queries) | |||
Pivot Table Totals | Excel Discussion (Misc queries) | |||
Pivot Table - Sub totals | Excel Discussion (Misc queries) | |||
Pivot table totals | Excel Discussion (Misc queries) | |||
Pivot Table Totals | New Users to Excel |