Can I use VBA to format every Pivotfield in a table that contains header P021 with a specific background colour

Hi everybody,

I have a Pivottable, that includes a number of columns with sales figures for different years. for each year I have an account in the Pivotcolumns called P021 that I would like to give a backgroundcolour that are for instance green.

But I am not able to give that column a background colour other than by formatting the individual cells.

How can I colour the whole Pivotcolumn in a green colour by VBA?

best regards

Jørgen
LVL 4
JorgenConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Jørgen,

It would be useful to see some sample data and/or a sample file. EE now allows you to directly upload files
to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible
to fully and permanently delete it. Therefore, be very careful about posting proprietary, confidential, or
other sensitive information. If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types. If your file type does not match
those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run
by people connected to EE.

Patrick
0
NorieVBA ExpertCommented:
Jorgen

Are you sure you need VBA?

If this was 'normal' data, ie not a pivot table, you could use conditional formatting and I can't
think of any reason why you couldn't use it with a pivot table.

The formulas might not be a little more complicated as usual mind you.
0
JorgenConsultantAuthor Commented:
Hi Imnorie and Patrick,

I need the VBA to format the data as I need the process to be automated and I will not be responsible of the process apart from implementing it.

The users that will be updating and controlling this will be finance people with very little experience in both Pivot tables and conditional formatting. And they will not know much about how to upload data either.

Patrick I will upload a file as soon as I get 10 minutes, but I have to modify data, as they are customer confidential, and therefore can not be uploaded.

Jørgen
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

JorgenConsultantAuthor Commented:
Hi Patrick

I have hereby uploaded the file. I had to change a little bit in titles, so the issue is now that all basic figures (not totals) in Columns that includes the header 10/11 YTD should have the bachground colour White. that all basic figures (not totals) in Columns that includes the header 09/10 YTD should have the bachground colour Blue. that all basic figures (not totals) in Columns that includes the header 10/11 Budget should have the bachground colour Green.  And I Need a border round all Volumes areas for instance the Pivotfields included in column E and F

Jørgen
Customer-Analysis-Example-file.xls
0
Patrick MatthewsCommented:
I still don't see why you can't use Conditional Formatting for this.I did the following, successfully:1) Selected E4:IV65536 on the sheet with the PT2) Added a formula-based CF rule using the following formula:=AND(E4<>"",COUNTIF(E$3,"*10/11 YTD*"),COUNTIF($A4:$D4,"*total*")=0)
0
JorgenConsultantAuthor Commented:
Hi Patrick,

I can partly solve the problem in this way, But as (which I should have told you) I am working in Excel 2003 I only have 3 conditions to set, I can get my colouring, but not the borders, and I will only colour cells that have a content, not cells that are empty but part of the area of the pivot table.

For the border I can make a border round the individual cells, but not around the whole area of the pivot table. In this case it could for instance be the Area E3:F95.

This has been my reasoning for trying to set the background colour for the whole area of the Pivottable.

Hopefully this gives more insight of the requirement.

Jørgen
0
Patrick MatthewsCommented:
Sorry, I had misread your last comment.The borders will be a problem, but the coloring can still be done with Conditional Formatting.  I used these CF formulas:=AND(E4<>"",COUNTIF(E$3,"*Budget 10/11*"),COUNTIF($A4:$D4,"*total*")=0)    (for green)=AND(E4<>"",COUNTIF(E$3,"*09/10 YTD*"),COUNTIF($A4:$D4,"*total*")=0)     (for blue)No rule is needed for the white coloring, because your default background is already white.BTW, you do not have any headings that include "10/11 Budget".  You do have some with "Budget 10/11", and thus that is what I used.
0
NorieVBA ExpertCommented:
Jørgen

Can you explain all the formatting you want to apply?

As has been said borders can be a problem, conditional formatting doesn't allow certain types of formatting for some reason.

What that reason is I can't recall, it's lost in the mists of time I think.

Do you still want to see if it's possible using code?

I think it might be and might not be as difficult as I first thought, though I'll probably need to take a look at your file to see if that's right.
0
Rory ArchibaldCommented:
The only bit that looks tricky to me for code is avoiding the totals rows. Other than that you just use the pivotfield's data range property.
0
NorieVBA ExpertCommented:
Rory

I've still not had a look at the file but that's the sort of thing I was thinking of using.

As you probably know a pivot table has quite a few range properties for different parts.

I think using them and perhaps a little bit of resizing/offsetting the formatting could be done in code.
0
JorgenConsultantAuthor Commented:
Hi Rory,

My hope was that I could use the Pivotfields data range property, but I seem not to be able to find the right code for it.

I want to do this in VBA, as this template is to be the standard of a number of reporting pivottables and that I should not use a lot of time updating all other peoples pivottables.

I have uploaded a new file, where I in column G and H have showed how my columns should look ideally both with columns and background fill.

I can generate the code for the conditionally formatting, that Patrick suggested on the conditionally formatting, but I still do not feel that my report has a look that the users will find brilliant.

I am only working as a consultant for the company, and they spend hours formatting their pivottables - and they actually use pivottables to an extend that I have never experienced before without having much knowledge of how to optimize.

Jørgen
Customer-Analysis-Example1-file.xls
0
Rory ArchibaldCommented:
Why are the rows coloured green different from the rows coloured yellow? And why do the two gross sales columns have borders around them? (that doesn't seem to match your question)
0
Rory ArchibaldCommented:
Is this the sort of thing you had in mind?



Sub FormatPivot()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim strField As String
    Set pt = ActiveSheet.PivotTables(1)
    
    Application.ScreenUpdating = False
    
    For Each pf In pt.DataFields
        strField = UCase$(pf.Caption)
        If InStr(1, strField, "BUDGET 10/11", vbBinaryCompare) > 0 Then
            FormatRangeColour pf.DataRange, 6 ' yellow
        ElseIf InStr(1, strField, "YTD 10/11", vbBinaryCompare) > 0 Then
            FormatRangeColour pf.DataRange, 4 ' green
        End If
    Next pf
    
    Application.ScreenUpdating = True

End Sub
Sub FormatRangeColour(rng As Range, lngCI As Long)
    Dim rngCell As Range
    rng.Interior.ColorIndex = lngCI
    For Each rngCell In rng
        With rngCell.PivotCell
            If .PivotCellType = xlPivotCellBlankCell Then
                rngCell.Interior.ColorIndex = xlColorIndexNone
            ElseIf .RowItems.Count < .Parent.RowFields.Count Then
                rngCell.Interior.ColorIndex = xlColorIndexNone
            End If
        End With
    Next rngCell
End Sub

Open in new window

0
JorgenConsultantAuthor Commented:
Hi Rory,

Yes it is absolutely something like this, that I Need. The only issue on the colouring side is that for Totals it removes the colouring from the total columns, which is acceptable, but if it could be solved, it would be perfect.

The colours are there to show which data groups that are related to each other, for instance that actual volume for the year is related to actual sales for the year and to the KPI's calculated for actual data for the year.
The borders is created to show a specific group of data together for instance volumes Year to Date and the comparison to Volumes Budget Year to date and Volumes Last Year to date.

Jørgen
0
Rory ArchibaldCommented:
>>"The only issue on the colouring side is that for Totals it removes the colouring from the total columns"
I'm not following this - where specifically should there be colour but isn't?
0
JorgenConsultantAuthor Commented:
For instance in G50 , or in any other of the columns, where the header is Strategic Brands Total and that is updated with the VBA code.
0
JorgenConsultantAuthor Commented:
Beforehand there was a background colour, but that is removed.
0
Rory ArchibaldCommented:
I specifically added that in because you hadn't coloured them in the sample! Before we carry on can you provide a sample that shows EXACTLY which rows should be coloured and which shouldn't?
0
JorgenConsultantAuthor Commented:
Hi Rory,

The lines that are coloured is fine, But from the automaticly format that was generated through the format report function in the Pivottoolbar some rows are coloured in advance. These lines are in this specific case line 50, 71, 73 and 93, but that can vary depending on the source. It is actually the totals of the highest hierarki (or the first rowfield chosen).

And if possible the formatting from the format report function should be kept if possible.

Jørgen

0
Rory ArchibaldCommented:
Sounds hideous, but I'd still like to see a sample that categorically shows exactly what you want as the end result.
0
JorgenConsultantAuthor Commented:
The ideal file will look like this. This also includes the borders, that I wrote about.

Jørgen
Customer-Analysis-Example-file.xls
0
Rory ArchibaldCommented:
How about this:



Sub FormatPivot()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim strField As String
    Dim n As Long, lngRowCount As Long
    
    Set pt = ActiveSheet.PivotTables(1)
    lngRowCount = pt.DataBodyRange.Rows.Count
    Application.ScreenUpdating = False
    
    For Each pf In pt.DataFields
        strField = UCase$(pf.Caption)
        If InStr(1, strField, "BUDGET 10/11", vbBinaryCompare) > 0 Then
            FormatRangeColour pf.DataRange, 6 ' yellow
        ElseIf InStr(1, strField, "YTD 10/11", vbBinaryCompare) > 0 Then
            FormatRangeColour pf.DataRange, 4 ' green
        ElseIf InStr(1, strField, "YTD 09/10", vbBinaryCompare) > 0 Then
            FormatRangeColour pf.DataRange, 3 ' red
        End If
    Next pf
    
    For n = 1 To pt.DataFields.Count Step 2
        pt.DataFields(n).LabelRange.Cells(1).Resize(lngRowCount + 1, 2).BorderAround xlContinuous, xlMedium
    Next n
    Application.ScreenUpdating = True

End Sub
Sub FormatRangeColour(rng As Range, lngCI As Long)
    Dim rngCell As Range
    rng.Interior.ColorIndex = lngCI
    For Each rngCell In rng
        With rngCell.PivotCell
            If .PivotCellType = xlPivotCellBlankCell Then
                rngCell.Interior.ColorIndex = xlColorIndexNone
            ElseIf .RowItems.Count = 1 Then
                rngCell.Interior.ColorIndex = rngCell.Offset(0, -1).Interior.ColorIndex
            ElseIf .RowItems.Count < .Parent.RowFields.Count Then
                rngCell.Interior.ColorIndex = xlColorIndexNone
            End If
        End With
    Next rngCell
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JorgenConsultantAuthor Commented:
Hi Rory,

Thanks for a great job done. This code will help both me and my customer in the future.

It also taught me about a few things about VBA for pivot tables, that I have not found in any books regarding VBA and Pivot tables.

Jørgen
0
NorieVBA ExpertCommented:
Jørgen

Is that really how the data needs to be presented?

I don't know why but it seems a bit cumbersome.

Note I'm not saying don't use whatever format you want/need, just that it might be worth considering a few changes.

Perhaps just something like adding a page field and resizing some of the columns.

PS Just tried Rory's code but it appears Excel 2000 is missing a few pivot objects.
0
JorgenConsultantAuthor Commented:
Hi Imnorie

No that is not how the data is presented but I already had the code for formatting the different fields as either rows or fields. I also have the formats, that shows the different kind of numberformats.

I only had to figure out the formats for the border and the backgroundcolouring. I have not put it in the excelfile. as I wrote to you guys that it was customer confidential data ,that I had to change before uploading it to the EE universe.

Actually when I have finished the template, a file will be taken from a datawarehouse,and my excelfile will be updated with the latest actual data. Then the pivottable will be updated and all the formats will be added. Finally the file is sent to the relevant users.

But I was glad for all the comments given even if the VBA code Rory send was the solotion I was looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.