Multiple Intersections, Conditional Formating

[bschulze]

I'm looking to apply a cell highlight to all intersections where the row and column heading match. The data set will be a pivot table, but if this prohibits the application of formatting I can work with a table. Solution needs to be Excel 2007 compatible. An example of what I'm looking for below.

Any help would be greatly appreciated, conditional formatting is not my strong suit.

Excel 2007
	A	B	C	D	E	F
2	Type	2	6	8	9	15
3	1
4	2
5	3
6	4
7	5
8	6
9	7
10	8
11	9
Sheet1

 

[svendiamond]

Under conditional formatting, "use a formula to determine which cells to format:"

=MATCH($A1,A$1,0)

And under "applies to" choose your entire table so =$A$1:$F$11 or however large your table is

Kinda confused about the way your table looks with row 1 saying "A B C D E F" ... not sure if you meant that to be the actual top of the excel sheet or what... but either way MATCH works just arrange it however you'd like

 

[mikerickson]

I would use conditional formatting on B3, with the formula =($A3=B$2)
and copy the formatting to the other cells.

 

[bschulze]

Thanks Sven, I see this works for a table setup, and idea if this is possible with a Pivot Table where the range would be automatically adjusted? FYI...the HTML creator inserts the A, B, C... and 1, 2, 3 along the Column and Rows respectfully. Perhaps a VDA solution using an AfterUpdate on the Pivot Table refresh?

 

[mikerickson]

Did you try either of our solutions? (adjusting to your actual cell references)?

 

[bschulze]

Mike, Sven's Match formula worked. Your formula was shifted one cell over and up (-1C and -1R). Neither adjusted automatically for a range change. Perhaps I'm missing something? In conditional formatting you need to highlight the range to apply it to right?

 

[mikerickson]

Apply my formula in B3 (test to see that it works right for you) and then copy that cell. Select the rest of your range and PasteSpecial>Validation.

 

[bschulze]

Thank Mike, but when the range of the Pivot Table changes neither solutions accounts for the change as the range is predefined in the conditional formatting. Any thoughts on a solution for a PT?

 

[svendiamond]

Can you just change the affected range to a very large area to cover however large your pivot table may be? I use Excel a lot but I actually have very little experience with pivot tables.

 

[bschulze]

Sven, I don't believe your solution will work that way, but Mike's should. I haven't tested just have a feeling. I'd really like to avoid a sloppy solution as the range can change greatly and this will end up in users hands, who will inevitably find a way to break it. I'd prefer a pivot table solution, but if I need to code a table solution I will. Thanks Sven and Mike.

Anyone else have thoughts on a pivot table solution?