I made a macro that makes this pivot table in excel. The pivot table works great but I want to add a few lines of code so that 3 columns of the pivot table will be conditionally formated. The problem is the columns won't be the same every time.

Here is a photo of the range I am trying to set with code, so I can do the conditional formating:

1.jpg

Once I figure this out I'm sure it won't be bad getting the other 2 columns to conditially format as well.


2.jpg

Here is the code I have written so far.

   Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    'MsgBox LastRow

    Dim lastrowminusone As Long
    lastrowminusone = LastRow - 1
          
    
    Range("A4").Select
    
    Do
    ActiveCell.Offset(0, 1).Select
    Loop Until ActiveCell = "Grand Total"
    ActiveCell.Offset(2, 0).Select

 'Tells you location
Dim RowLocation As Long 'can hold over 32000 if over this many rows
Dim ColumnLocation As Integer 'columns won't exceed 256 in sheet
Dim CellLocation As String
 
CellLocation = ActiveCell.Address
RowLocation = ActiveCell.Row
ColumnLocation = ActiveCell.Column
My approach was to start at A4 (which will always be the same starting point) and then search right until "grand total" is found. Then go down 2, which will always be the same. Then I need to find a way to highlight down and select the range but not including the very bottom number (it's 770 there)

Once that is done I can just do the conditional formating while I'm recording and copy the code for that right?