Shading pivot table based on value in 1st column

[BRC]

Hi Al
I am looking for a way to format (shade) a pivot table to make it
easier to read. I would like to alternate the background color. For
simplicity sake lets say the table has student name in col A and the
classes for that student in col B. So student 1 (S1) might have three
classes so there would be three rows devoted to S1 but Student 1 only
occurs in col A for the first occurrence (class) then col A is blank
until the next student appears. There are actually about 12 columns of
data for each class .
The closest post I found relating to this was titled “Shading rows of
with similar data” which discussed using conditiional formatting to
accomplish something very similar to what I am trying to do but I
tried and could not get the code to run. That post used the code:
=MOD(SUMPRODUCT(($A$1:$A1<>"")/(COUNTIF($A$1:$A1;$A$1:$A1)+($A$1:$A
$1="")))­,2) But Excel 2010 had problems with this code and it would
not execute.
Any thoughts or direction on this would be greatly appreciated. Thanks
BRC

 

[AB]

Perhaps i'm missing something but i'm not sure i saw anywhere in your
post what exactly you wanted to shade and based on what criteria...
Would it be the rows for the same student to be shaded differently
from the student above and below it?

 

[BRC]

Perhaps i'm missing something but i'm not sure i saw anywhere in your
post what exactly you wanted to shade and based on what criteria...
Would it be the rows for the same student to be shaded differently
from the student above and below it?



- Show quoted text -

Sorry I wasn't clear about that. Yes, I want to shade that section
related to a student to distinguish from the student above/below. So
in my example the first 3 rows would be shaded differently. I am
trying to create something like "greenbar" where the bars alternate
based on change in student. Thanks for looking. BRC

 

[AB]

I don't know what's wrong with your formula (maybe the fact that Pivot
doesn't have the student name in every row... (i.e., it's blank for
non-first rows)?
Check out this link:
http://www.cpearson.com/excel/ContentBanding.aspx
Chip there has an elegant solution using helper column. For you to use
the same approach you'd need to adjust it somewhat (for that same
above mentioned 'pivot blank cells' reason).
I tried it and it works but:
- you'll need to adjust the solution somewhat
- you'll be left with a helper column.

 

[Gpompidou]

Hi Al
I am looking for a way to format (shade) a pivot table to make it
easier to read.  I would like to alternate the background color.   For
simplicity sake lets say the table has student name in col A and the
classes for that student in col B.  So student 1 (S1) might have three
classes so there would be three rows devoted to S1 but Student 1 only
occurs in col A for the first occurrence (class) then col A is blank
until the next student appears. There are actually about 12 columns of
data for each class .
The closest post I found relating to this was titled “Shading rows of
with similar data” which discussed using conditiional formatting to
accomplish something very similar to what I am trying to do  but I
tried and could not get the code to run.  That post used the code:
=MOD(SUMPRODUCT(($A$1:$A1<>"")/(COUNTIF($A$1:$A1;$A$1:$A1)+($A$1:$A
$1="")))­,2)   But Excel 2010 had problems with this code and it would
not execute.
Any thoughts or direction on this would be greatly appreciated. Thanks
BRC

Try the subroutine below:

Sub GreenBarPivotTable()
'---Assign some color codes.
Const iClr1 = 6 'Yellow
Const iClr2 = 4 'Green

'---Defining some variables.
Dim iClr As Integer
Dim rEnd As Long
Dim cBeg As Integer
Dim cEnd As Integer
Dim rBegOfRng As Long
Dim rEndOfRng As Long
Dim rngToHiLite As Range
Dim cel As Range

'---Clear the slate of any color/shading.
Cells.Interior.ColorIndex = xlNone

'---Initialization of variables.
' In a pivot table, this assumption s/b OK.
cBeg = 1
' Last column of data.
cEnd = ActiveSheet.UsedRange.Columns.Count
' Find the bottom of the data block. Note the subtraction.
rEnd = Cells(Rows.Count, 1).End(xlUp).Row - 1
' Assign color code.
iClr = iClr1
' Begin the process with the very last cell (just above Grand
Total).
' The variable "cel" here is the subtotal cell for each item.
Set cel = Cells(rEnd, cBeg)

'---Cycle through the data to highlight each block.
Do
' Assuming each item has its own subtotal,
' the bottom of each range to be highlighted
' is one line above this subtotal row.
rEndOfRng = cel.Row - 1
' The beginning of the range is determined
' using the equivalent of CTRL+UPPARROW.
rBegOfRng = cel.End(xlUp).Row

' Define the range to be highlighted.
Set rngToHiLite = Range(Cells(rBegOfRng, cBeg),
Cells(rEndOfRng, cEnd))
' Shade the relevant range.
rngToHiLite.Interior.ColorIndex = iClr

' Toggle the color code.
If iClr = iClr1 Then
iClr = iClr2
Else
iClr = iClr1
End If

' Redefine the reference cell to be the next item above.
Set cel = cel.End(xlUp).Offset(-1, 0)
Loop Until Right(cel, 5) <> "Total"

End Sub

 

[minimaster]

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.Count - 1)).Select
Set PivotItemSelect = Union(PivotItemSelect, Selection)
PivotItemSelect.Select
End If
End If
err.Clear
On Error GoTo 0
End Function