Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Pivot Tables : Highlighting data in a PivotTable

  1. #1
    Join Date
    25th January 2004
    Posts
    12
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th January 2004
    Posts
    12
    Hello Andy !

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

    Dan

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th January 2004
    Posts
    12
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  6. #6
    Join Date
    25th January 2004
    Posts
    12
    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    25th January 2004
    Posts
    12
    Thanks for the help Andy.

    Danny

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 6
    Last Post: October 16th, 2005, 02:56
  2. Replies: 2
    Last Post: March 23rd, 2004, 15:14
  3. [Solved] Pivot Tables : PivotTable Ignore Blanks
    By steveski in forum EXCEL HELP
    Replies: 4
    Last Post: March 3rd, 2004, 00:38
  4. Replies: 7
    Last Post: February 10th, 2004, 09:27
  5. Replies: 1
    Last Post: September 18th, 2003, 01:13

Bookmarks

Posting Permissions

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