Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default Shading pivot table based on value in 1st column

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shading of Multiple Rows in a Pivot Table JanT Excel Worksheet Functions 1 May 9th 08 12:24 AM
Column shading color based on specific cell content bliten_bsas Excel Programming 1 October 30th 06 02:15 PM
Conditional Cell Shading (based on the shading of other cells) Tubby Excel Worksheet Functions 2 June 20th 06 10:03 PM
Column based cell shading rbmcc[_2_] Excel Programming 4 April 6th 06 06:27 PM
"Cumulative" column based on a pivot table sum column? Robert Day Excel Worksheet Functions 2 December 15th 05 09:20 PM


All times are GMT +1. The time now is 07:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"