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 :
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()) & "" & 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
I would also like to not highlight the values in the totals columns.
Would greatly appreciate any help .
Video Tutorials / Excel Dashboards Reports