Hi guys
This is driving me to dispair.
I have set two rules to format the data body range of a pivot table.
The first rule changes the colour of the font to blue for cells whose value is between 0 and 8.
The second rule changes the colour of the font to red for cells whose value is greater than 8.
Cells with an exact value of 8 should remain with its font colour in black.
First of all I make sure that no old conditional formatting rules are functioning by deleting them.
pt.DataBodyRange.FormatConditions.Delete
'Pt is the variable that points to my pivot table
'Now I create the first formatting condition
pt.DataBodyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=0.1", Formula2:="=7.99"
'Once created I apply the formatting
pt.DataBodyRange.FormatConditions(1).Font.Color = -1003520 (blue)
'This makes the font blue for cells whose value is between 0 and 8
'So far no problem. The cells that meet the condition are displayed with a blue font.
'Now I create a new rule
pt.DataBodyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=8"
'and for cells that meet this rule (value greater than 8) I want their font in red
pt.DataBodyRange.FormatConditions(2).Font.Color = -16776961(this is red colour)
'Note that I am applying this formatting to the second rule created. But instead of colouring in red the cells greater than 8, it applies the formatting to the first set of cells, the ones with their value bigger than 0 and smaller than 8.
'If I try something like
.DataBodyRange.FormatConditions(2).Interior.ThemeColor = xlThemeColorAccent3
'changing this time the interior colour it works without any problem, and the formatting this time applies to cells greater than 8 as it should.
'If I go to the conditional formatting menu and try to do it manually I have no problem setting different font colours for different rules, but for some reason it doesn't work when doing it programmatically.
Any help?
Thanks.
This is driving me to dispair.
I have set two rules to format the data body range of a pivot table.
The first rule changes the colour of the font to blue for cells whose value is between 0 and 8.
The second rule changes the colour of the font to red for cells whose value is greater than 8.
Cells with an exact value of 8 should remain with its font colour in black.
First of all I make sure that no old conditional formatting rules are functioning by deleting them.
pt.DataBodyRange.FormatConditions.Delete
'Pt is the variable that points to my pivot table
'Now I create the first formatting condition
pt.DataBodyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=0.1", Formula2:="=7.99"
'Once created I apply the formatting
pt.DataBodyRange.FormatConditions(1).Font.Color = -1003520 (blue)
'This makes the font blue for cells whose value is between 0 and 8
'So far no problem. The cells that meet the condition are displayed with a blue font.
'Now I create a new rule
pt.DataBodyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=8"
'and for cells that meet this rule (value greater than 8) I want their font in red
pt.DataBodyRange.FormatConditions(2).Font.Color = -16776961(this is red colour)
'Note that I am applying this formatting to the second rule created. But instead of colouring in red the cells greater than 8, it applies the formatting to the first set of cells, the ones with their value bigger than 0 and smaller than 8.
'If I try something like
.DataBodyRange.FormatConditions(2).Interior.ThemeColor = xlThemeColorAccent3
'changing this time the interior colour it works without any problem, and the formatting this time applies to cells greater than 8 as it should.
'If I go to the conditional formatting menu and try to do it manually I have no problem setting different font colours for different rules, but for some reason it doesn't work when doing it programmatically.
Any help?
Thanks.
