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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

nzo12

New Member
Joined
Mar 16, 2016
Messages
5
I've tried every variation I can find on the web. Is it even possible to do what I'm asking?
 

nzo12

New Member
Joined
Mar 16, 2016
Messages
5
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.
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,033
Members
414,356
Latest member
death20

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
Top