Highlight Minimum values in a pivot table

[gingerafro]

I have a very large data set (about 450k rows) of hotels with a price for each day for a whole year.

I have put them in a pivot table with dates down the left and hotels across the top. price is obviously the data field.

I want to show for each day of the year, which hotel is the cheapest by highlighting it. I have tried running a macro and the basics of it are below.

Sub dateselect()

Dim d As PivotItem
Dim b As PivotItem

With Worksheets("Sheet1").PivotTables("PivotTable5")
For Each d In .PivotFields("START DATE").PivotItems
For Each b In .PivotFields("NIGHT PRICE (MKT CURR)").PivotItems
If b = Min(d) Then
.PivotItems(b).Font.Color = 2
End If
Next
Next
End With
End Sub

If I was doing this manually, I would look down at each date then look across to find the cheapest price. I had hoped my macro would do it, but my VBA writing is a bit 'pigeon'.

 

[Andrew Poulsom]

You could use Conditional Formatting:

Sub Test()
    With Worksheets("Sheet1").PivotTables("PivotTable5").DataBodyRange
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=" & .Cells(1, 1).Address(False, False) & "=MIN(" & Range(.Cells(1, 1), .Cells(1, .Columns.Count)).Address(False, True) & ")"
        .FormatConditions(1).Interior.ColorIndex = 35
    End With
End Sub

 

[gingerafro]

Andrew, once again I am in your debt!

many thanks.

 

[gingerafro]

Hi Andrew

For this to work properly, I need to select the top left corner of the pivot table. Can this be done automatically?

thank you!

 

[Andrew Poulsom]

Why do you need to select anything. My code uses the PivotTable's DataBodyRange.

 

[gingerafro]

I'm not trying to select anything. The code only works if the cell highlighted at the time of running the macro is the top left of the pivot's data body.

If the cell is in the middle of the pivot all sorts of cells get highlighted. I think it is something to do with the 'address' part of the code.

 

[Andrew Poulsom]

That seems to be a bug, because Formula1 is a text expression that should get entered as is. Try:

Sub Test()
    With Worksheets("Sheet1").PivotTables("PivotTable5").DataBodyRange
        .Cells(1, 1).Select
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=" & .Cells(1, 1).Address(False, False) & "=MIN(" & Range(.Cells(1, 1), .Cells(1, .Columns.Count)).Address(False, True) & ")"
        .FormatConditions(1).Interior.ColorIndex = 35
    End With
End Sub

 

[gingerafro]

Hi Andrew,

Its not pretty, and therefore probably not the best way, but I have added to your code and it is working.

Sub Test()

Worksheets("Sheet1").Range("A1:IV65535").FormatConditions.Delete

Worksheets("Sheet1").PivotTables("PivotTable5").DataBodyRange.Select

With Worksheets("Sheet1").PivotTables("PivotTable5").DataBodyRange
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=" & .Cells(1, 1).Address(False, False) & "=MIN(" & Range(.Cells(1, 1), .Cells(1, .Columns.Count)).Address(False, True) & ")"
.FormatConditions(1).Interior.ColorIndex = 35
End With
End Sub


thank you for your time and help.
ps. when I get a minute, I'll have a think about why it didn't work first off. I agree with you that as you chose the pivot's DataBodyRange it should have worked.

 

[gingerafro]

I told you mine wasn't pretty.
Your second code is doing the trick now - thank you very much.