Pivot Table DataBodyRange for a dynamic range selection in Cell Formula

[SMCC]

Copy of table is attached.

To the right of the Pivot Table on the second sheet is 6 cells that summarize payments for the jobs that are completed.
If a job is unpaid, the cell is left uncolored.
If a job is paid, it will be colored either Yellow or Green depending on which branch it pertains to.

I wish to make this procedure more automated by using a dynamic range selection in VBA that way I can call instead of manually highlighting the desired area in the pivot table.
My current method is shown in the attached spreadsheet. However I am having trouble and I don't know what to do next.

Please Login or Register  to view this content.

That way in Cell I3, Sheet 2, from the attached example, I can enter (something similar to)

Please Login or Register  to view this content.

So that my range can be automatically found instead of manually dragging a box around what I want. The pivot tables generated from month to month is no always going to be the same size.

Just to be clear, the range I wish to capture is the following.
PTDataBodyRange.png
Excluding the Grand Total Column and Row.

Any help is much appreciated.
Please ask questions if I have not explained my idea correctly.

Thanks,

SMCC

[CK76]

One way to do it.

Please Login or Register  to view this content.

Then your formula in I5 becomes...
=SumCellsByColor(GetRange("PivotTable1"),H5)

Alternately you can use following and use cell reference to any cell within PivotTable.

Please Login or Register  to view this content.

I5 then becomes...
=SumCellsByColor(GetPvtRange(A3),H5)

[SMCC]

Thanks, CK76!

[CK76]

You are welcome and thanks for the rep