Solved

VBA Excel 2000 - Conditional Formatting of Pivots

Posted on 2011-02-16
3
459 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
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

856 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