CODEWITHSUNDEEP
vbaexcel
Ivan Gutierrez
Ivan Gutierrez

Reputation: 73

How to color in the cells below a dynamic pivot table?

I am new to VBA, I have a pivot table that through filtering will change in size (rows not columns) and I want to color in the cells below the last row a certain color. I have the following code but I get the error "Object doesn't support this property or function" when the 4th line of the code is executed. From my understanding I am declaring the variable rlastcell as a range and thus the cells function should work on this variable. 

Dim pvt As PivotTable
For Each pvt In Worksheets("Summary").PivotTables
      Dim rlastcell As Range
      rlastcell = pvt.Cells(pvt.Rows.Count, pvt.Columns.Count)
      rlastcell.Offset(1, 0).Interior.ColorIndex = RGB(0, 31, 96)
      rlastcell.Offset(1, -1).Interior.ColorIndex = RGB(0, 31, 96)
      Next pvt

Upvotes: 0

Views: 1132

Answers (1)

QHarr
QHarr

Reputation: 84465

After getting rid of any existing fill you could do something like the following where these lines set the row underneath the pivottables dynamically by offsetting and resizing from the original table range.

With pvt.TableRange1
    .Offset(.Rows.Count, 0).Resize(1, .Columns.Count)

Code:

Option Explicit
Public Sub test()
    Dim pvt As PivotTable
    For Each pvt In Worksheets("Summary").PivotTables
        With pvt.TableRange1
            .Offset(.Rows.Count, 0).Resize(1, .Columns.Count).Interior.Color = vbRed  ' = RGB(0, 31, 96)
        End With
    Next
End Sub

Change colour as required.

Upvotes: 1

Related Questions