Announcement

Collapse
No announcement yet.

Pivot Tables : Highlighting data in a PivotTable

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pivot Tables : Highlighting data in a PivotTable

    Hello !

    I have created a pivot table to show down hours for pieces of equipment. The pivot table shows the accumulated down hours in the following format ex: 120:33:12 . I'm trying to highlight in the pivot table any equipment that has over 50 hrs of downtime.

    I'm trying to use a macro but can't figure out how to handle the 120:33:12 which is an accumulation of time.
    Here is a copy of the code :

    Sub DownhrsFormatting()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rngPTBody As Range
    Dim i As Range

    Set ws = ActiveWorkbook.Worksheets("Dec Info")
    Set pt = ws.PivotTables(1)
    'Return the range containing the data
    ' in the pivot table
    Set rngPTBody = pt.DataBodyRange


    'x = Int(Sum([50])) & "" & Mid$(Format((x - Int(x)) / 24, "Long Time"), 3, 6)
    x = Format(50, "Long time")

    'loop through all of the cells that comprise
    ' the data body area


    For Each i In rngPTBody
    'if value is greater than 200
    'change the back color to Red

    If Format(i.Cells.Value, "[hh]:mm:ss") > x Then
    i.Cells.Interior.Color = vbRed
    End If

    Next

    End Sub


    I would also like to not highlight the values in the totals columns.

    Would greatly appreciate any help .

    Thanks,
    Dan

  • #2
    Hi Dan and welcome.

    I don't think the VBA Format command works the same as the display formats used on the worksheet.
    [hh]:mm:ss works for the cell but returns :01:00 , which is not much use.

    Try this revised code, including restriction of cell highlighting.
    Sub DownhrsFormatting()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rngPTBody As Range
    Dim i As Range
    Dim x As Single

    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Set pt = ws.PivotTables(1)
    'Return the range containing the data
    ' in the pivot table
    Set rngPTBody = pt.DataBodyRange
    x = 50 / 24 ' Format(50, "Long time")
    'loop through all of the cells that comprise
    ' the data body area
    For Each i In rngPTBody
    'if value is greater than 200
    'change the back color to Red
    If i.Row = rngPTBody.Rows(rngPTBody.Rows.Count).Row Then
    ' ignore total row
    ElseIf i.Column = rngPTBody.Columns(rngPTBody.Columns.Count).Column Then
    ' ignore total column
    Else
    If i.Cells.Value > x Then
    i.Cells.Interior.Color = vbRed
    End If
    End If
    Next

    End Sub


    Cheers
    Andy

    Cheers
    Andy

    Comment


    • #3
      Hello Andy !

      Thanks for the help....it works like a charm !

      Dan

      Comment


      • #4
        I've notice when the table is collapsed and I run the macro it looks fine until I expand the table then I notice it has highlighted cells that are less than 50 hrs.When I did a test when the pivot table was expanded it worked fine . Is there a way in the macro to expand it before highlighting the fields and than collapse it after.

        I also notice that it does not ignore the subtotals. It ignores the grandtotals.

        Many Thanks,
        Dan

        Comment


        • #5
          Hi Labman

          Could you not use Conditional Formatting?

          Comment


          • #6
            Hi Dave,

            I played with that for awhile but I wasn't able to put in a fixed value it only seemed to work with a cell reference. I tried put a value of 50 inside a cell outside of the pivot table but it didn't seem to reference it.

            Thanks,
            Dan

            Comment


            • #7
              Hi,

              The conditional formating doesn't appear to move with the cells.
              So if you select all the cells in the table, excluding total column/row, and apply

              Cell Value is - Greater than - =50/24
              Format as red interior.
              When you remove an item from the table the total column or row now has conditional formating applied.

              The problem is that the rows/columns are not collapsed but re-written.
              Therefore you would still need to run code everytime the table changed.

              Try this in the code module of the sheet.

              Private Sub Worksheet_Calculate()
              DownhrsFormatting
              End Sub

              Cheers
              Andy

              Cheers
              Andy

              Comment


              • #8
                Thanks for the help Andy.

                Danny

                Comment

                Working...
                X