VBA / Pivot Table / Comparing values from two fields and highlighting

[LEXCERM]

Hi,

Hoping you can help.

From the example Pivot (below), I need to highlight any [Country] that does not equal GB and has a [Confirmed] status of YES (bearing in mind we deal with many different countries so hardcoding each country is not an option):-

I've tried using many different codes and although I am able to highlight specific values for one PivotField, I am unable to work out how to compare different fields within a Pivot.

Here are a couple of snippets of what I have been working with which has changed a multitude of times:-

If Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Confirmed").PivotItems("YES") = "YES" _
And Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Country").PivotItems("GB") <> "GB" Then
Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Country").PivotItems("GB").LabelRange.Interior.Color = vbYellow
End If

------------------------

Dim PvtTbl As PivotTable
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range

Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")
Set rng1 = PvtTbl.PivotFields("Confirmed").DataRange
Set rng2 = PvtTbl.PivotFields("Confirmed").PivotItems("YES").LabelRange
Set rng3 = PvtTbl.PivotFields("Country").DataRange
Set rng4 = PvtTbl.PivotFields("Country").PivotItems("GB").LabelRange

If Intersect(rng2, rng4) Is Nothing Then
Intersect(rng3, rng4).Interior.Color = vbYellow
End If


Thanks in advance for your help.

 

[LEXCERM]

Hi,

Hoping you can help.

From the example Pivot (below), I need to highlight any [Country] that does not equal GB and has a [Confirmed] status of YES (bearing in mind we deal with many different countries so hardcoding each country is not an option):-

View attachment 79348

I've tried using many different codes and although I am able to highlight specific values for one PivotField, I am unable to work out how to compare different fields within a Pivot.

Here are a couple of snippets of what I have been working with which has changed a multitude of times:-

If Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Confirmed").PivotItems("YES") = "YES" _
And Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Country").PivotItems("GB") <> "GB" Then
Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Country").PivotItems("GB").LabelRange.Interior.Color = vbYellow
End If

------------------------

Dim PvtTbl As PivotTable
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range

Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")
Set rng1 = PvtTbl.PivotFields("Confirmed").DataRange
Set rng2 = PvtTbl.PivotFields("Confirmed").PivotItems("YES").LabelRange
Set rng3 = PvtTbl.PivotFields("Country").DataRange
Set rng4 = PvtTbl.PivotFields("Country").PivotItems("GB").LabelRange

If Intersect(rng2, rng4) Is Nothing Then
Intersect(rng3, rng4).Interior.Color = vbYellow
End If


Thanks in advance for your help.

So I am getting closer, and knocked-up this code which isn't quite right-

Dim PvtTbl As PivotTable
Dim PvtFld As PivotField
Dim PvtFld2 As PivotField
Dim PvtItm As PivotItem
Dim PvtItm2 As PivotItem

Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")

Set PvtFld = PvtTbl.PivotFields("Confirmed")
Set PvtFld2 = PvtTbl.PivotFields("Country")

For Each PvtItm In PvtFld.PivotItems
For Each PvtItm2 In PvtFld2.PivotItems

If PvtItm.Name = "YES" And PvtItm2.Name <> "GB" Then
PvtItm2.DataRange.Interior.Color = vbRed
End If
Next PvtItm2

Next PvtItm


Even though PvtItm.Name and PvtItm2.Name are picking up ="YES" and <>"GB", it colours in the "NO" values as well:-

Thanks for listening!

 

[LEXCERM]

So the code is highlighting just <>"GB" and excluding the "Confirmed" argument of "YES", hmmmm:-