Solved

VBA Excel 2000 - Conditional Formatting of Pivots

Posted on 2011-02-16
3
486 Views
Last Modified: 2012-06-27
Dear Experts,

Could you please check the attached Code or the Modul1 macro in the attached file, basically it is a simple pivot creation.

My target would be to find some alternative to this line

    Range("E11:E22").Select

so when the pivot is created, I select certain cells for conditional formatting. But the problem is that sometimes I will not know which will be the exact range, as the number of lines can change.

Is there maybe some method which could refer to the pivot name itself so to PivotTable1, and based on that doing Conditional Formatting on maybe RowFields or ColumnFields categories?

thanks,
Sub PivotCreation()

Dim LastRowWithValue As Long
LastRowWithValue = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & LastRowWithValue & "C3").CreatePivotTable TableDestination:="", _
        TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=Workbooks("PivotVBATemplate.xls").Worksheets("Sheet2").Cells(9, 1)
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
        "Item", ColumnFields:="Area"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Qty")
        .Orientation = xlDataField
        .NumberFormat = "# ##0"
        .Function = xlSum
    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area")
        .PivotItems("West").Visible = False
    End With

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").PivotItems( _
        "South").Position = 1

    Range("E11:E22").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="2000"
    Selection.FormatConditions(1).Interior.ColorIndex = 4
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="2000"
    Selection.FormatConditions(2).Interior.ColorIndex = 3

End Sub

Open in new window

PivotVBATemplate.xls
0
Comment
Question by:csehz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 34907999
Yes, VBA can be used to select almost any subset of a pivot table. I would do it like this, using a With statement after a PivotSelect statement:

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataOnly, True
    With Selection
      .FormatConditions.Delete
      'The rest of the formatting statements go here, beginning with .FormatConditions
   End With

This will also include the row/column grand total cell at the bottom, but to remove the formatting from that, just do this:

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Column Grand Total'", xlDataOnly, True
    Selection.FormatConditions.Delete

I also noticed that your code is creating a blank worksheet and then placing the pivot table on Sheet2. To avoid this, specify the destination initially rather than setting it in a separate line. That will require activating the sheet in order to keep the "Activesheet" statements that are next. The full code with all of my recommendations is attached.
Sub PivotCreation()

Dim LastRowWithValue As Long
LastRowWithValue = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & LastRowWithValue & "C3").CreatePivotTable TableDestination:=ActiveWorkbook.Worksheets("Sheet2").Range("A9"), TableName:="PivotTable1"
ActiveWorkbook.Worksheets("Sheet2").Activate
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
        "Item", ColumnFields:="Area"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Qty")
        .Orientation = xlDataField
        .NumberFormat = "# ##0"
        .Function = xlSum
    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area")
        .PivotItems("West").Visible = False
    End With

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").PivotItems( _
        "South").Position = 1

    'With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").PivotItems("South").DataRange
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataOnly, True
    With Selection
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
            , Formula1:="2000"
        .FormatConditions(1).Interior.ColorIndex = 4
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="2000"
        .FormatConditions(2).Interior.ColorIndex = 3
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Column Grand Total'", xlDataOnly, True
    Selection.FormatConditions.Delete
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:csehz
ID: 34914583
Telyni19 thanks very much, you even solved the additional sheet problem :-))) Thanks just again for it.

Running your code anyway I am getting a message "Run-time error '450' - Wrong number of arguments or invalid property assignment" at row

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataOnly, True

Can be maybe related that I have Excel 2000 so some version problem?

0
 
LVL 1

Author Closing Comment

by:csehz
ID: 34915215
Telyni19 finally I found it, yes probably some version problem between excels, my one waits that row in this format

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Row Grand Total'", xlDataAndLabel

Your codes works perfectly for me, thanks the help
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question