Solved

VBA Excel 2000 - Conditional Formatting of Pivots

Posted on 2011-02-16
3
431 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
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now