Option Explicit
Sub test()
Dim tcd As PivotTable
With Sheets("tcd")
If .PivotTables.Count > 0 Then .PivotTables(1).TableRange2.Delete
Set tcd = .PivotTableWizard(xlDatabase, Sheets("stat").Range("A1").CurrentRegion, .Range("A1"))
End With
With tcd
.AddDataField .PivotFields("tg_nat"), "Occurences", xlCount
.AddDataField .PivotFields("tg_nat"), "Nombre de tg_nat", xlCount
.AddDataField .PivotFields("Q_compenser"), "Min de Q_compenser", xlMin
With .PivotFields("tg_nat")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Nombre de tg_nat")
[COLOR="#FF8C00"] .FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SOMME($C$5:$C5)<=0,8"[/COLOR]
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599963377788629
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions(1).ScopeType = xlFieldsScope
End With
With .DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
.PivotFields("tg_nat").AutoSort xlDescending, "Occurences", .PivotColumnAxis.PivotLines(1), 1
End With
End Sub