sporadic conditional formatting problem

D

DeathSurfer

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
 
V

Vergel Adriano

I think it's because if for example, your selection started at row 7, your
conditional formulas will be off by one row.

For example, try it this way for the first conditional formula:

Dim lRow As Long
lRow = Selection.Row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
With Selection.FormatConditions(1)
.Interior.ColorIndex = 2
.Font.Bold = False
.Font.ColorIndex = 1
End With

try the same approach for the other conditional formulas...



--
Hope that helps.

Vergel Adriano


DeathSurfer said:
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
Click to expand...
 
D

DeathSurfer

Vergel:

I tried what you posted and it doesn't seem to work. This is what my code
looks like after the change:

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

'remove all conditional formatting on pivot table
pvt.PivotSelect "Sum of Yards to be Cut[all]", xlDataOnly, True

Dim lRow As Long
lRow = Selection.Row

'make cells white that are covered by the available fabric
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow & "+$E" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))>=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))>=$D" & lRow & "+$E" & lRow
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I selected cell M67 after running the code and the conditional format
formula reads this:

=(SUM($H53:M53))<=$D53+$E53

this made the cell yellow and there isn't a value in the cell. Is there
possibly some kind of cached data that needs to be deleted?


Vergel Adriano said:
I think it's because if for example, your selection started at row 7, your
conditional formulas will be off by one row.

For example, try it this way for the first conditional formula:

Dim lRow As Long
lRow = Selection.Row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
With Selection.FormatConditions(1)
.Interior.ColorIndex = 2
.Font.Bold = False
.Font.ColorIndex = 1
End With

try the same approach for the other conditional formulas...



--
Hope that helps.

Vergel Adriano


DeathSurfer said:
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
Click to expand...
Click to expand...
 
V

Vergel Adriano

It's weird that you're not getting the correct result.. I used your current
code, selected M67 and ran the code. The resulting formula is

=(SUM($H67:H67))<=$D67+$E67

which is correct, right? There seem to be a typo with summing H67:H67 but
that's how it was in your original post...

If you're always just running the code against the activecell, you can try

lRow=ActiveCell.Row

but I can't think of any reason why the code you have now would not give the
correct row number...


--
Hope that helps.

Vergel Adriano


DeathSurfer said:
Vergel:

I tried what you posted and it doesn't seem to work. This is what my code
looks like after the change:

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

'remove all conditional formatting on pivot table
pvt.PivotSelect "Sum of Yards to be Cut[all]", xlDataOnly, True

Dim lRow As Long
lRow = Selection.Row

'make cells white that are covered by the available fabric
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow & "+$E" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))>=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))>=$D" & lRow & "+$E" & lRow
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I selected cell M67 after running the code and the conditional format
formula reads this:

=(SUM($H53:M53))<=$D53+$E53

this made the cell yellow and there isn't a value in the cell. Is there
possibly some kind of cached data that needs to be deleted?


Vergel Adriano said:
I think it's because if for example, your selection started at row 7, your
conditional formulas will be off by one row.

For example, try it this way for the first conditional formula:

Dim lRow As Long
lRow = Selection.Row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
With Selection.FormatConditions(1)
.Interior.ColorIndex = 2
.Font.Bold = False
.Font.ColorIndex = 1
End With

try the same approach for the other conditional formulas...



--
Hope that helps.

Vergel Adriano


DeathSurfer said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

DeathSurfer

Vergel:

The resulting formula you should have got based on how I think the
conditional formula should work and the fact that you selected cell M67 is:

=(SUM($H67:M67))<=$D67+$E67

I'm selecting a huge range of information in the pivot table that these
conditional formulas are being applied to. I need the formulas to be
calculated by the row number that the cell is in that has the conditonal
formula. For example:

--------------------------------------------------------
A | B | C | D | E | F | G | H | I |
--------------------------------------------------------
1 | 1 | 20 | 5 | 2 | | | | | |
2 | 5 | 10 | 6 | | 4 | | | | |
3 | | 9 | | 5 | | | 34 | | |
4 | | | | | | | | | |
5 | | | | | | | | | |
6 | | | | | | | | | |

lets say that range A1:I6 got selected as the pivot table data above and
this would be the range that the conditional formatting is applied. If a
similar formula to the one in my code is:

=(SUM($C1:C1))>=$A1+$B1

and this gets applied to every cell in the selected range, then if you
select cell G3 from above the formula should read I think:

=(SUM($C3:G3))>=$A3+$B3

and if the condition was to change the cell color to red the cell should be
red in this case because 5+34 is >= 9

But again my code will grab some random row number and will grab a random
cell for the G3 above for some reason. But if I re-run the code a couple of
times it will clear itself up and work perfectly. I believe I have run into
some kind of excel glitch but I'm not sure. I have at this point recreated
the worksheet that this data is on, I've made sure that my conditional
formatting is the last thing I do to the pivot table and it still on occasion
messes up.


Vergel Adriano said:
It's weird that you're not getting the correct result.. I used your current
code, selected M67 and ran the code. The resulting formula is

=(SUM($H67:H67))<=$D67+$E67

which is correct, right? There seem to be a typo with summing H67:H67 but
that's how it was in your original post...

If you're always just running the code against the activecell, you can try

lRow=ActiveCell.Row

but I can't think of any reason why the code you have now would not give the
correct row number...


--
Hope that helps.

Vergel Adriano


DeathSurfer said:
Vergel:

I tried what you posted and it doesn't seem to work. This is what my code
looks like after the change:

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

'remove all conditional formatting on pivot table
pvt.PivotSelect "Sum of Yards to be Cut[all]", xlDataOnly, True

Dim lRow As Long
lRow = Selection.Row

'make cells white that are covered by the available fabric
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow & "+$E" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))>=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))>=$D" & lRow & "+$E" & lRow
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I selected cell M67 after running the code and the conditional format
formula reads this:

=(SUM($H53:M53))<=$D53+$E53

this made the cell yellow and there isn't a value in the cell. Is there
possibly some kind of cached data that needs to be deleted?


Vergel Adriano said:
I think it's because if for example, your selection started at row 7, your
conditional formulas will be off by one row.

For example, try it this way for the first conditional formula:

Dim lRow As Long
lRow = Selection.Row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
With Selection.FormatConditions(1)
.Interior.ColorIndex = 2
.Font.Bold = False
.Font.ColorIndex = 1
End With

try the same approach for the other conditional formulas...



--
Hope that helps.

Vergel Adriano


:

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
Click to expand...
Click to expand...
Click to expand...
 
D

DeathSurfer

Vergel:

Just to let you know I think I figured out a solution to my problem but
don't understand why this works. If I put the following code in front of and
after the conditional formatting then I don't get this sporatic behavior from
the conditional formatting:

ws.Range("A1").Select

As you can see all I'm doing is selecting cell "A1". For some reason I don't
have any problems now with the conditions. Maybe you would understand why
this would solve this issue.

Thanks for all your help.

Deathsurfer

DeathSurfer said:
Vergel:

The resulting formula you should have got based on how I think the
conditional formula should work and the fact that you selected cell M67 is:

=(SUM($H67:M67))<=$D67+$E67

I'm selecting a huge range of information in the pivot table that these
conditional formulas are being applied to. I need the formulas to be
calculated by the row number that the cell is in that has the conditonal
formula. For example:

--------------------------------------------------------
A | B | C | D | E | F | G | H | I |
--------------------------------------------------------
1 | 1 | 20 | 5 | 2 | | | | | |
2 | 5 | 10 | 6 | | 4 | | | | |
3 | | 9 | | 5 | | | 34 | | |
4 | | | | | | | | | |
5 | | | | | | | | | |
6 | | | | | | | | | |

lets say that range A1:I6 got selected as the pivot table data above and
this would be the range that the conditional formatting is applied. If a
similar formula to the one in my code is:

=(SUM($C1:C1))>=$A1+$B1

and this gets applied to every cell in the selected range, then if you
select cell G3 from above the formula should read I think:

=(SUM($C3:G3))>=$A3+$B3

and if the condition was to change the cell color to red the cell should be
red in this case because 5+34 is >= 9

But again my code will grab some random row number and will grab a random
cell for the G3 above for some reason. But if I re-run the code a couple of
times it will clear itself up and work perfectly. I believe I have run into
some kind of excel glitch but I'm not sure. I have at this point recreated
the worksheet that this data is on, I've made sure that my conditional
formatting is the last thing I do to the pivot table and it still on occasion
messes up.


Vergel Adriano said:
It's weird that you're not getting the correct result.. I used your current
code, selected M67 and ran the code. The resulting formula is

=(SUM($H67:H67))<=$D67+$E67

which is correct, right? There seem to be a typo with summing H67:H67 but
that's how it was in your original post...

If you're always just running the code against the activecell, you can try

lRow=ActiveCell.Row

but I can't think of any reason why the code you have now would not give the
correct row number...


--
Hope that helps.

Vergel Adriano


DeathSurfer said:
Vergel:

I tried what you posted and it doesn't seem to work. This is what my code
looks like after the change:

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

'remove all conditional formatting on pivot table
pvt.PivotSelect "Sum of Yards to be Cut[all]", xlDataOnly, True

Dim lRow As Long
lRow = Selection.Row

'make cells white that are covered by the available fabric
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow & "+$E" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))>=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))>=$D" & lRow & "+$E" & lRow
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I selected cell M67 after running the code and the conditional format
formula reads this:

=(SUM($H53:M53))<=$D53+$E53

this made the cell yellow and there isn't a value in the cell. Is there
possibly some kind of cached data that needs to be deleted?


:

I think it's because if for example, your selection started at row 7, your
conditional formulas will be off by one row.

For example, try it this way for the first conditional formula:

Dim lRow As Long
lRow = Selection.Row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
With Selection.FormatConditions(1)
.Interior.ColorIndex = 2
.Font.Bold = False
.Font.ColorIndex = 1
End With

try the same approach for the other conditional formulas...



--
Hope that helps.

Vergel Adriano


:

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
Click to expand...
Click to expand...
Click to expand...
 
V

Vergel Adriano

hmn. that is really weird. I tested your code again and this time I even
created a pivot table. ran your code and it worked for me. it selected the
pivot data range and applied conditional formats. nothing got mixed up.
Perhaps it's version specific? I have XL2003 with SP2.


--
Hope that helps.

Vergel Adriano


DeathSurfer said:
Vergel:

Just to let you know I think I figured out a solution to my problem but
don't understand why this works. If I put the following code in front of and
after the conditional formatting then I don't get this sporatic behavior from
the conditional formatting:

ws.Range("A1").Select

As you can see all I'm doing is selecting cell "A1". For some reason I don't
have any problems now with the conditions. Maybe you would understand why
this would solve this issue.

Thanks for all your help.

Deathsurfer

DeathSurfer said:
Vergel:

The resulting formula you should have got based on how I think the
conditional formula should work and the fact that you selected cell M67 is:

=(SUM($H67:M67))<=$D67+$E67

I'm selecting a huge range of information in the pivot table that these
conditional formulas are being applied to. I need the formulas to be
calculated by the row number that the cell is in that has the conditonal
formula. For example:

--------------------------------------------------------
A | B | C | D | E | F | G | H | I |
--------------------------------------------------------
1 | 1 | 20 | 5 | 2 | | | | | |
2 | 5 | 10 | 6 | | 4 | | | | |
3 | | 9 | | 5 | | | 34 | | |
4 | | | | | | | | | |
5 | | | | | | | | | |
6 | | | | | | | | | |

lets say that range A1:I6 got selected as the pivot table data above and
this would be the range that the conditional formatting is applied. If a
similar formula to the one in my code is:

=(SUM($C1:C1))>=$A1+$B1

and this gets applied to every cell in the selected range, then if you
select cell G3 from above the formula should read I think:

=(SUM($C3:G3))>=$A3+$B3

and if the condition was to change the cell color to red the cell should be
red in this case because 5+34 is >= 9

But again my code will grab some random row number and will grab a random
cell for the G3 above for some reason. But if I re-run the code a couple of
times it will clear itself up and work perfectly. I believe I have run into
some kind of excel glitch but I'm not sure. I have at this point recreated
the worksheet that this data is on, I've made sure that my conditional
formatting is the last thing I do to the pivot table and it still on occasion
messes up.


Vergel Adriano said:
It's weird that you're not getting the correct result.. I used your current
code, selected M67 and ran the code. The resulting formula is

=(SUM($H67:H67))<=$D67+$E67

which is correct, right? There seem to be a typo with summing H67:H67 but
that's how it was in your original post...

If you're always just running the code against the activecell, you can try

lRow=ActiveCell.Row

but I can't think of any reason why the code you have now would not give the
correct row number...


--
Hope that helps.

Vergel Adriano


:

Vergel:

I tried what you posted and it doesn't seem to work. This is what my code
looks like after the change:

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

'remove all conditional formatting on pivot table
pvt.PivotSelect "Sum of Yards to be Cut[all]", xlDataOnly, True

Dim lRow As Long
lRow = Selection.Row

'make cells white that are covered by the available fabric
'Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))<=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow & "+$E" & lRow
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($" &
rgR_Rush & ":" & rgR_Rush & "))>=$" & rgR_NetAvail & "+$" & rgR_OpenPO
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))>=$D" & lRow & "+$E" & lRow
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

I selected cell M67 after running the code and the conditional format
formula reads this:

=(SUM($H53:M53))<=$D53+$E53

this made the cell yellow and there isn't a value in the cell. Is there
possibly some kind of cached data that needs to be deleted?


:

I think it's because if for example, your selection started at row 7, your
conditional formulas will be off by one row.

For example, try it this way for the first conditional formula:

Dim lRow As Long
lRow = Selection.Row
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(SUM($H" &
lRow & ":H" & lRow & "))<=$D" & lRow
With Selection.FormatConditions(1)
.Interior.ColorIndex = 2
.Font.Bold = False
.Font.ColorIndex = 1
End With

try the same approach for the other conditional formulas...



--
Hope that helps.

Vergel Adriano


:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Formatting Code 1
Conditional formating Run-time error '5' 0
4 conditional formats 2
Conditional Formatting Code 4
1004 Error in VBA Conditional Formatting Code – Fails in 2007 0
Conditional Formatting 5
Pivot Conditional Formatting 4
VBA Conditional formating 3

Top