Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like to highlight the row of the pivot table where the RowRange contains the word "Total". The RowRange consists of two criteria ie Col A & Col B and the DataRange is Col C. As an example: if cell A7 contained the word "Total", Range("A7:C7") would be highlighted.

    The code below first apprears to work but the highlighting does not stick.


    Sub Test_It ()
    Dim pvt As PivotTable
    Dim rng As Range

    Set pvt = ActiveSheet.PivotTables(1)

    For Each rng In pvt.RowRange.Cells
    If InStr(rng.Value, "Total") Then
    rng.Interior.ColorIndex = 15
    rng.Offset(0, 1).Interior.ColorIndex = 15
    rng.Offset(0, 2).Interior.ColorIndex = 15
    Else
    rng.Interior.ColorIndex = xlColorIndexNone
    End If
    Next

    End Sub



    Thanks for taking a look,
    John

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Silly me...Rem out the "Else" statement.

    John




    [quote name='jstevens' post='771440' date='20-Apr-2009 13:08']I would like to highlight the row of the pivot table where the RowRange contains the word "Total". The RowRange consists of two criteria ie Col A & Col B and the DataRange is Col C. As an example: if cell A7 contained the word "Total", Range("A7:C7") would be highlighted.

    The code below first apprears to work but the highlighting does not stick.


    Sub Test_It ()
    Dim pvt As PivotTable
    Dim rng As Range

    Set pvt = ActiveSheet.PivotTables(1)

    For Each rng In pvt.RowRange.Cells
    If InStr(rng.Value, "Total") Then
    rng.Interior.ColorIndex = 15
    rng.Offset(0, 1).Interior.ColorIndex = 15
    rng.Offset(0, 2).Interior.ColorIndex = 15
    Else
    rng.Interior.ColorIndex = xlColorIndexNone
    End If
    Next

    End Sub



    Thanks for taking a look,
    John[/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •