• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Conditional formatting for Pivot tables !

ianb

ianb

Member
Hi All,

conditional formatting does not work around a pivot table if asking for all grey after the pivot tabel has more then less columns except for the left column under the pivot table when changed using ="" and the colur grey.

I was attempting to write a VBA program for everytime a pivottable is changed.

I need to have this so the front part of the spreadsheet does not move from the position of the change or the button click.

Can any one advise on the changes to this program. I will be using a spinner button or the pivot table button to change the pivot tables. I would like to keep the grey colour at the background (in all cells) and the pivot table remains white.


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Sub CondFormat_Indiv()
Dim PrevCell As Range
Set PrevCell = ActiveCell
Range("A83:ac159").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="""""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
PrevCell.Select
'Range("e79").Select
End Sub
 
Hi Ian ,

I am not able to understand your requirement ; if you wish to format the entire pivot table , then surely a statement referencing the pivot table is in order e.g.

Target.TableRange1.Interior.ThemeColor = xlThemeColorDark1

What is the range A83:AC159 you are using in your macro ? And why do you need a formatcondition ?

Narayan
 
It is around the pivot table I would like to keep the background grey. the pivot table is white.

If you use conditional formating in excel 2007-2010 then it does not keep the conditional formatting when the pivot table move less in rows and then more. it leave the formatting around the pivot table white. hence I can not use conditional formatting. a VBA program would work yet the location of the cursor after the pivot table change does not look smooth nor does the applying of the program. I would like the program to work in the background not showing the program selecting.

A83:AC159 is the range of the conditinal formatting fo the grey. the pivot table will always remain white.

Format Conditon is when the cell value is blank.
 
conditinal formatting - format only cells that contain. - cell value equals =""

Format - Grey colour selected.

try and move a pivot table up and down in columns and you will see that the formatting does not work.

I am looking for a solution to keep the grey background surrounding the pivot table grey at all times.
 
Hi Ian ,

Sorry , but I am still confused ; someone else is sure to chip in.

As far as I can see , a single statement such as :

Target.TableRange1.Interior.Color = xlNone

ensures the entire pivot table is white in color.

If you color your entire worksheet range what ever color you want , and the next statement is the above statement , the pivot table area alone will be white.

Narayan
 
Hi Ian ,

When I used the following lines , the problem I faced earlier is no longer there :
Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
        Range("A1:H3600").Interior.Color = vbRed
        Target.PreserveFormatting = True
        Target.TableRange1.Interior.Color = xlNone
End Sub

Initially , without the PreserveFormatting statement , I would get gray coloured areas amidst the red when I filtered the pivot table ; after using the statement , this problem has disappeared.

Narayan
 
I think I have the same issue as Ian. It seems as though the ranges selected for Conditional Formatting in the Pivot Table change as the Selection Criteria changes. I have the PT Option Preserve Formatting checked. I am testing this new workbook for this month and realize that the rows and columns will change as the selection changes which requires daily resetting the Conditional Format range.

I can Conditionally Format the data manually but would rather use a macro to do this for me.

I thought I could help myself by creating a Range Name and using that name to assign the Conditional Format range. I used the OFFSET formula to create this Named Range: ThisWeekPTData. The PT titles row is on 7 and the first row of data begins on row 8. This OFFSET formula seems to work properly by adjusting the range automatically: =OFFSET(ThisWeek!$B$7,1,0,COUNTA(ThisWeek!$A:$A)-4,COUNTA(ThisWeek!$7:$7)-2 ). At least the Named Range is adjusting itself correctly, but the CF ignores the RangeName after the criteria is changed.

I have attached a sample workbook with all the data. The date in Data!N2 is set to 9/18/2013 and the Conditional Formatting is applied the way I'd like to see it. Change the date to the week before, 9/11/2013, all the CF disappears. Change the date back to 9/18 and the CF is restored (or never went away for those dates).

I tried to Record a macro that would do the Conditional Format using the Named Range (tacking that on to the end of the PT Refresh) but haven't had any luck. The range still changes with the selection. What suggestions can you offer?
 

Attachments

  • Book2.xlsm
    608.2 KB · Views: 11
Hi ,

Can you check your file now ?

I have added a Worksheet_PivotTableUpdate procedure , which initializes 3 variables , and uses these to restore the conditonal format.

Narayan
 

Attachments

  • Buck_Book2.xlsm
    546.4 KB · Views: 12
Thanks Narayank991, but I pointed you to the wrong macro. Please look at the attached wks and macro PivotConditionalFormat (assigned to the button on sheet ThisWeek). I've done some reasearch and a bunch of trial and error (mostly error).

When I press the macro button the CF actually flashes right before my eyes, and then disappears just as quickly.

I added the 19th data to the db, so if you change the date in Data!N2 the refresh the PT you can see that the CF does not automatically reassign to the new criteria. So with this new code (below and in the attached wks module) I would like to Delete and then Add New Conditional Formatting.

Sub PivotConditionalFormat()
' Macro to add Conditional Formatting to Pivot Table
' for pivot table on wks ThisWeek
'
Range("a7").Select

'Delete existing CF
With ActiveSheet
.PivotTables("PT_ThisWeek").PivotSelect "Date", xlDataOnly, True
Selection.FormatConditions.Delete
End With
'Add New CF
With ActiveSheet
.PivotTables("PT_ThisWeek").PivotSelect "Date", xlDataOnly, True
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249946592608417
End With

Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions(1).ScopeType = xlSelectionScope
End With
End Sub
 

Attachments

  • Rejected_Bills_2013-09.xlsm
    650.6 KB · Views: 6
Sorry, the background around the pivot table is grey and the pivot table is white.

Conditional formatting does not remain the grey background when the pivot tabel data is refreshed and the item length shortens. the background shows white not grey.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Sub CondFormat_Indiv()
Dim PrevCell As Range
Set PrevCell = ActiveCell
Range("A83:ac159").Select ' the range around the pivot table that is grey
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="""""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
PrevCell.Select
'Range("e79").Select
End Sub
 
Hi Ian ,

Please understand that Conditional Formatting needs to be used only when conditions are involved ; if you wish to color the worksheet area one color , and the pivot table another color , then you do not need CF.

Try this :

Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
        With Range("A83:AC159").Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249946592608417
        End With
        Target.PreserveFormatting = True
        Target.TableRange1.Interior.Color = xlNone
End Sub
Narayan
 
Thank you Narayan. This works perfectly. How does it work? I can see that in this workbook the CF updates automatically, even when I changed the dates in Data to months 10 and 11. I don't even need the macro button, simply Refresh the PT and the CF adjusts along with the new dates in the PT.

I would like to be able to use this in other workbooks by recycling your code. I don't pretend to understand it all just yet but I can follow some of the code. So part of my understanding will come from applying your code to my original workbook by Copy-Paste(ing) your additions into my original file. But when I tried, I don't get the same results.

I know that you added this Worksheet_PivotTableUpdate procedure and these two macros (below). What other steps will I need to get this to work?
Public fcf1 As Variant
Public fcic As Variant
Public fcat As Variant

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Set dbr = Target.DataBodyRange
If Target.ColumnGrand Then Set dbr = dbr.Resize(, dbr.Columns.Count - 1)
If Target.RowGrand Then Set dbr = dbr.Resize(dbr.Rows.Count - 1)
fcat = dbr.Address

fcf1 = "=B8>=1"
fcic = 3487637
Range(fcat).Select
With Selection
If .FormatConditions.Count <> 0 Then .FormatConditions.Delete
.FormatConditions.Add xlExpression, xlEqual, fcf1
.FormatConditions(1).Modify xlExpression, xlEqual, fcf1

.FormatConditions(1).Interior.Color = fcic
End With
Target.PreserveFormatting = True
End Sub


Public Sub PT_Thisweek_Refresh()
ActiveSheet.PivotTables("PT_Thisweek").RefreshTable
End Sub


Thanks,
Annon
 
Hi ,

I don't think there is anything to be customized in the code , other than the CF formula and the CF colour.

The button calls the subroutine PT_Thisweek_Refresh , which contains the pivot table name ; this will need to be changed if you are applying it to another pivot table.

My first coding put the three public variables as public because I intended to store the CF formula what ever it might have been , and then apply it when the pivot table was refreshed ; but currently , the CF formula has been hardcoded , and so the three variables fcf1 , fcic and fcat can be declared within the pivot table update procedure Worksheet_PivotTableUpdate as follows :

Dim fcf1 As Variant
Dim fcic As Variant
Dim fcat As Variant

If you can upload the new file where you have problems , I can check it out.

Narayan
 
Hi Narayan, I understand. your coding is very close to the solution i require. If I have 4 pivot tables it is colouring the background grey which is what I require (around 4 pivot tables) yet also colouring the three tables I did not update grey inside. the one table I did change goes white. can you advise please. many thanks for your time and energy in helping me with my dashboard visually. any further input is welcome.


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
With Range("A78:ac159").Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Target.PreserveFormatting = True
Target.TableRange1.Interior.Color = xlNone

End Sub
 
Hi Ian ,

Do you mean to say that the range you have mentioned viz. A78:AC159 , also contains other pivot tables ?

If so , we can use a loop to ensure that all pivot tables on the sheet are without colour :

Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
            With Range("A78:ac159").Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249946592608417
            End With
           
            Target.PreserveFormatting = True
            For Each pt In Target.Parent.PivotTables
                pt.TableRange1.Interior.Color = xlNone
            Next
End Sub
Narayan
 
Narayank991 - you are a STAR !!! - it is the finishing touch on my Automated Multi Dashboard.

The dashboard is completed....... wooohooo !!!! Many thanks...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pt As PivotTable
With Range("A83:ac342").Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With

Target.PreserveFormatting = True
For Each pt In Target.Parent.PivotTables
pt.TableRange1.Interior.Color = xlNone
Next
End Sub
 
Narayank, I decided to try to recreate my original file from scratch (nearly) to include your code. But I don't get the results I got before. Please check it out and let me know what I've missed or done wrong.

Thanks again (and again).
 

Attachments

  • Retry.xlsm
    697.8 KB · Views: 11
Hi ,

Check your file now.

The complete sequence of events is as follows :

1. The user clicks the button Button 1 , on the ThisWeek worksheet

2. This runs the macro PT_Thisweek_Refresh ; this macro can be anywhere , since you are assigning a macro to the button , and you can specify the complete path to the macro ; if it is in a module , it can be referred to by its name , if it is in the code section for a worksheet , say Sheet2 , you can assign it as :

Sheet2.PT_Thisweek_Refresh

3. When the pivot table is refreshed , the event procedure Worksheet_PivotTableUpdate is triggered automatically. However , since this is an event procedure related to a worksheet , it needs to be in the code section for that worksheet ; a workbook can have more than one procedure named Worksheet_PivotTableUpdate , with each of them placed in the respective worksheet sections.

The problem was that the Worksheet_PivotTableUpdate procedure had been placed in a module.

Thus , since the code section for the ThisWeek tab did not have an associated Worksheet_PivotTableUpdate procedure , nothing happened.

Narayan
 

Attachments

  • Buck_Retry.xlsm
    696.4 KB · Views: 45
You must log in or register to reply here.
Back
Top