How to put shade in every other row without Conditional Format on a Pivottable?

[feipezi]

Hi,

I worked on some PTs created by someone who left the company. They have shade in every other row but there is no CF. I tried Format as Table but it won't work on PT. It's good for regular tables only, I guess.

Is there any other tricks that make it happen for PTs?

Thanks in advance.

 

[kjv1611]

Sounds like there is some table formatting options set on that pivottable. Should be under the PivotTableTools tab, at least i think that's what it's called - I see it all the time, but not looking at the moment.

Otherwise, it'd of been manually done or scripted via VBA I would guess.

I think I've seen the option to do just what you mention under formatting for the pivottable.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Hi,

What version of Excel?

Versions 2007+ select in the PT and use a table shading format (one of dozens) in the table pop up tabs in the Ribbon.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Thanks for the quick return!

Btw, Excel version is 2010.

I followed what you guys said. Select entire PT -> (go back to Select dropdown again) select Values (because I only want DataRange to have shade) -> Design, trying to pick PivotTable Styles (similar to Format as Table) but nothing happens.

The following is the code I set up to serve the same purpose, with the help of CF. It works. But if I could have it done without the macros, I would remove the code and let the Excel built-in functionality handle the situation.

Thanks again.


Sub PTViaADO_0902()
Dim Con As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim sql As String
Dim PTCache As PivotCache
Dim pt As PivotTable

Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DATA\FED_CHARGEBACK.mdb;"

sql = "SELECT * FROM FED_REPORT_FINAL_CITYST "

sql = "SELECT Customer, [Cust Name], [Cust City], [Cust State], [Cust Nmbr],PRODUCT, st, MONTH, [WAC SALES], [RAW UNITS], NET " & _
"FROM FED_REPORT_FINAL_CITYST"

Set RS = New ADODB.Recordset
Set RS.ActiveConnection = Con

RS.Open sql
On Error Resume Next
Sheets("Sheet1").UsedRange.Clear
On Error GoTo 0
'Create the PivotTable cache....
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set PTCache.Recordset = RS

Set pt = PTCache.CreatePivotTable _
(TableDestination:=Sheets("Sheet1").Range("a2"), _
TableName:="one")

With pt
.AddFields PageFields:=Array("Cust City", "Cust State", "Cust Name", "Customer")
.PivotFields("PRODUCT").Orientation = xlRowField
With .PivotFields("MONTH")
.Orientation = xlColumnField
.NumberFormat = "mmm-yy"
End With
With .PivotFields("NET")
.Orientation = xlDataField
.Function = xlSum
.Caption = "Feder Net Sales"
.NumberFormat = "$#,##0"
End With
End With
Call cf
Sheets("Sheet1").Activate
End Sub

Sub cf()
Dim pt As PivotTable
Dim combo As Range
Sheet10.Activate
Set pt = ActiveSheet.PivotTables(1)
Set combo = Union(pt.RowRange, pt.DataBodyRange)
combo.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=0"
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
End With
pt.PivotSelect "", xlDataAndLabel, True
Selection.Style = "Input"
Range("A8").Select
End Sub

Sub cleargarb()
srow = Cells(1, 1).PivotTable.TableRange1.Row
erow = Cells(srow, 1).End(xlDown).Row
Range(Cells(erow + 1, 1), Cells(1000, 27)).Clear
scol = Cells(1, 1).PivotTable.TableRange1.Column
ecol = Cells(1, 1).PivotTable.TableRange1.Columns.Count
Range(Cells(1, ecol + 1), Cells(1000, ecol + 100)).Clear
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.ScreenUpdating = False
Call cf
Call cleargarb
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Select entire PT"

Absolutely not necessary!

Simply select any cell in the PT.

When you do this a context sensitive thing appears in the Ribbon, named PivotTable Tools, with 2 Tabs: Options & Design.

In the Design Tab is a PivotTable Styles Group. Open the scroll to see all the canned styles. YOU can create a New PivotTable Style... I've never played with that but I'm sure a member of your substance could do a fine job.

All kinds of new horizons!!!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}