Intersect PivotFields & RowFields

nzo12

New Member
Joined
Mar 16, 2016
Messages
5
What I want it to do is intersect rows "Emp. Name" & "Avg Salary". I can then apply Font.Color filters to specific values down the line, but for now I can't figure out how to select the correct intersects.This is what the pivot looks like.
Values
Row LabelsAvg. SalaryData #1Data #2 Data #3
Manager #175,000
Emp. Name50,000
Emp. Name100,000
Manager #250,000
Emp. Name25,000
Emp. Name75,000

<tbody>
</tbody>
Here is what I have so far but its not working. Essentially, it is applying the Font.Color changes to the entire "Avg Salary" Column instead of only to the "Emp Names" in "Avg Salary".

Code:
Dim rng1 As Range    Dim rng2 As Range
Set rng1 = pvt.PivotFields("Avg Salary").DataRange
Set rng2 = pvt.RowFields("Employees").DataRange.EntireRow
Intersect(rng1, rng2).Font.Color = vbRed

This is what I want the code to do.
Values
Row LabelsAvg. SalaryData #1Data #2Data #3
Manager #175,000
Emp. Name50,000
Emp. Name100,000
Manager #250,000
Emp. Name25,000
Emp. Name75,000

<tbody>
</tbody>

Any help being able to find the correct intersect would be much appreciated.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I've tried every variation I can find on the web. Is it even possible to do what I'm asking?
 
Upvote 0
Thank you. I have been using that.

What I found is that this is getting me close.

Set rng1 = pvt.RowFields("Employees").PivotItems("Jon James").DataRange.EntireRow Set rng2 = pvt.PivotFields("Avg Salary").DataRange


Intersect(rng1, rng2).Font.Color = vbRed

This intersects the salary and row field but only for the employee "Jon James." I'm assuming my next step is to create an array using the employee names and pass that arrow into .PivotItems

I'll update the thread if I figure it out. Who knows, it may help someone down the line who is searching for a similar answer.
 
Upvote 0
Another approach would be to add a column to your source data to differentiate your employees... for example, the column could be labeled Color, and employee attributes could be red, blue, green...

Then your code could be...

Code:
Set rng1 = pvt.RowFields("Color").PivotItems("Red").DataRange.EntireRow

That would eliminate the need for an array.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top