Duuuuudes:

I'm not sure really what to ask here, but I am having a sporadic issue with
conditional formatting on a pivot table. I have the code below that is
applying 3 conditional formats to a pivot table's data. Before these
conditions are applied I have code that is deleting all conditional
formatting on the pivot table because I have this dynamically applying the
formats as the size of the pivot table changes. For some reason when applying
these formats, let's say cell J50 is active, the conditional format formula
for that cell should say =(SUM($H50:J50))<=$D50 according to the code below,
but it might actually be this =(SUM($H27:J27))<=$D27 even though J50 is
active, which is causing havoc on the way the formatting should be. I don't
understand the sporadic nature of the conditional format formula grabbing
what seems to be some random cells for the formula. The other confusing thing
is I can run the code again and it may work perfectly by having the right
cells in the formula, =(SUM($H50:J50))<=$D50. It's almost like some kind of
data needs to be refreshed but I'm not sure what. Can someone shed some light
on this?

Here's the conditional format code:

'turn off grand totals so they are not calculated in conditional formatting
pvt.RowGrand = False
pvt.ColumnGrand = False

'select data body range
pvt.PivotSelect "Sum of Yards to be Cut[all]", xlDataOnly, True

'make cells white that are covered by the available fabric
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=(SUM($H6:H6))<=$D6"
With Selection.FormatConditions(1)
.Interior.ColorIndex = 2
.Font.Bold = False
.Font.ColorIndex = 1
End With

'make cells yellow that are covered by the available fabric with the
addition of an open po
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=(SUM($H6:H6))<=$D6+$E6"
With Selection.FormatConditions(2)
.Interior.ColorIndex = 6
.Font.Bold = True
.Font.ColorIndex = 1
End With

'make cells red that are not covered by the available fabric with the
addition of an open po
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=(SUM($H6:H6))=$D6+$E6"
With Selection.FormatConditions(3)
.Interior.ColorIndex = 30
.Font.Bold = True
.Font.ColorIndex = 2
End With

'turn grand totals back on after formatting
pvt.RowGrand = True
pvt.ColumnGrand = True