Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Excel 2000 - Conditional Formatting of Pivots

Posted on 2011-02-16
3
Medium Priority
?
499 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

610 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