I would like to know how select a range of cells (using VBA) within a Pivot Table that belong to a specific category please. (as seen in the screenshot below)
My aim is to programmatically give those cells with a "1" a different colour based on the level of importance. For example HIGH will be red, and MEDIUM will be orange. This pic shows how I want the end result to look like:
The reason why this needs to be done in VBA is because I create many pivot tables based on the same categories but with different data. There could be many more rows within HIGH or a different number of columns based on the data source, so it needs a PivotTable VBA command that knows the chart's boundaries so that the relevant conditional formatting can be applied.
What I have figured out so far is the ability to select cells based on their category using the VBA code below, but the issue is it selects category totals rather than all cells within a category (screenshot below shows what the code actually selects, which are the cells to the right of HIGH)
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
I'm hoping someone on here has the technical know-how to help solve this please?
Attached is a temporary .xlsm which has my raw data (test values for this purpose), the pivot table, and the VBA code which does the selection.
Many thanks in advance,