Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
A routine that works specifically with any rowfield selection in a
pivot table. This allows alternating coloring for each row field that you wish to be colored alternating. Just select a cell in the label area of the rowfield that you like to get colored alternating and start ColorRowItemSelectionAlternating() Adjust the colorindexes to your liking. Sub ColorRowItemSelectionAlternating() Dim pf As PivotField Dim pfits() As PivotItem Dim r As Range Dim rAll As Range Dim c As Range Dim sw As Boolean Dim i As Integer Set r = Selection ' just for restoration at the end of the formating Set pf = r.Cells(1).PivotField If pf Is Nothing Then MsgBox "Make a cell selection inside a pivot table and try again!" Else pf.Parent.PivotSelect "'" & pf.Name & "'[All]", xlLabelOnly, True Set rAll = Selection i = 1 ReDim Preserve pfits(i) Set pfits(1) = rAll.Cells(1).PivotItem ' store pivotitems in array with same order as on worksheet For Each c In rAll If c.PivotItem.Name < pfits(i).Name Then i = i + 1 ReDim Preserve pfits(i) Set pfits(i) = c.PivotItem End If Next c For i = LBound(pfits) To UBound(pfits) ' lets color them alternating If Not PivotItemSelect(pf, pfits(i), xlLabelOnly) Is Nothing Then ' change mode to xlDataAndLabel if you like to color the data too. If sw Then Selection.Interior.ColorIndex = 35 Else Selection.Interior.ColorIndex = 34 End If sw = Not sw End If Next i End If r.Select End Sub Function PivotItemSelect(pf As PivotField, pfit As PivotItem, mode As XlPTSelectionMode) As Range err.Clear On Error Resume Next pfit.Parent.Parent.PivotSelect "'" & pf.Name & "'[" & pfit.Name & "]", mode, True If err.Number < 0 Then Set PivotItemSelect = Nothing Else Set PivotItemSelect = Selection If mode = xlLabelOnly And (pf.Subtotals(1) = True Or pf.Subtotals(2) = True) Then pfit.Parent.Parent.PivotSelect "'" & pf.Name & "'[" & pfit.Name & "]", xlDataAndLabel, True Range(Cells(Selection.Row + Selection.Rows.Count, Selection.Column), _ Cells(Selection.Row + Selection.Rows.Count, _ Selection.Areas(Selection.Areas.Count).Column + Selection.Areas(Selection.Areas.Count).Columns.Cou nt - 1)).Select Set PivotItemSelect = Union(PivotItemSelect, Selection) PivotItemSelect.Select End If End If err.Clear On Error GoTo 0 End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Shading of Multiple Rows in a Pivot Table | Excel Worksheet Functions | |||
Column shading color based on specific cell content | Excel Programming | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
Column based cell shading | Excel Programming | |||
"Cumulative" column based on a pivot table sum column? | Excel Worksheet Functions |