Border of cells around pivot table

**jomili** · 05-02-2013, 01:43 PM

I have a pivot table that's refreshed every time the sheet is activated. The design desired on the PT sheet is to have the cells around the pivot to be green, while the pivot remains uncolored, and the rest of the sheet other than the green border stays uncolored (or, stays the various colors it is). I've included an example of the desired result.

The problem is that, depending on the PT's source data, the pivot can be only a few rows deep, or many rows deep. Adding or removing a field, or changing the number of rows, changes where the green should be, so I need a macro tha can keep up with it. Can anyone point me in the right direction?

**dilipandey** · 05-02-2013, 02:15 PM

HI jomili,

Pivot can grow only downwards or towards right as well ?

I believe you can use conditional formatting using offset / defined names to obtain green boarder and after that it all untouched cells.

Regards,
DILIPandey
<click on below * if this helps>

**jomili** · 05-03-2013, 08:08 AM

I don't understand how I could use conditional formatting in this situation. Can you explain further?

**JosephP** · 05-03-2013, 08:59 AM

perhaps name the green area around the pivot table (including the pivot table itself) "pivotBorder" then add this code to the sheet

Please Login or Register  to view this content.

**jomili** · 05-03-2013, 09:55 AM

JosephP,
Again you save me! That works perfectly.

Can you explain the reasoning behind this line of code? I don't understand the reason for .Offset, though I can recognize offsetting -2, then adding 8 to row count ends up with 6 rows colored.

Please Login or Register  to view this content.

**Miraun** · 05-03-2013, 10:04 AM

The offset is declaring the point in which to start the resize function.

So, if you're table starts at C3, the offset is telling you to go to A2, and then add in the rows (+8) and all the columns (+2)... This is basically setting how far up and how far to the left the border starts.

Hope that helps!