ModifyAppliesToRange method for pivotfield
up vote
0
down vote
favorite
I'm trying to apply a formatting condition to a pivot field, the script below does that but applies Formatting to "Selected Cells" of the Pivot Filed and makes it a static cells range, instead of Pivot Field Values of the pivot table, where as the same can be done manually. Please look at the image attached for clear info.
ActiveSheet.PivotTables(PivotName).PivotFields("SPEC").DataRange.FormatConditions.Add Type:=xlNoBlanksCondition
With ActiveSheet.PivotTables(PivotName).PivotFields("CPR SPEC").DataRange.FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
End with
Image Shows the Formatting Rule selected from Script and the one Required to be Selected:
excel vba excel-vba pivot-table conditional-formatting
edited Nov 21 at 5:52
lagom
3,39151737
asked Nov 21 at 5:22
Kuridi Sunil
11
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
I'm trying to apply a formatting condition to a pivot field, the script below does that but applies Formatting to "Selected Cells" of the Pivot Filed and makes it a static cells range, instead of Pivot Field Values of the pivot table, where as the same can be done manually. Please look at the image attached for clear info.
ActiveSheet.PivotTables(PivotName).PivotFields("SPEC").DataRange.FormatConditions.Add Type:=xlNoBlanksCondition
With ActiveSheet.PivotTables(PivotName).PivotFields("CPR SPEC").DataRange.FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
End with
Image Shows the Formatting Rule selected from Script and the one Required to be Selected:
excel vba excel-vba pivot-table conditional-formatting
edited Nov 21 at 5:52
lagom
3,39151737
asked Nov 21 at 5:22
Kuridi Sunil
11
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm trying to apply a formatting condition to a pivot field, the script below does that but applies Formatting to "Selected Cells" of the Pivot Filed and makes it a static cells range, instead of Pivot Field Values of the pivot table, where as the same can be done manually. Please look at the image attached for clear info.
ActiveSheet.PivotTables(PivotName).PivotFields("SPEC").DataRange.FormatConditions.Add Type:=xlNoBlanksCondition
With ActiveSheet.PivotTables(PivotName).PivotFields("CPR SPEC").DataRange.FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
End with
Image Shows the Formatting Rule selected from Script and the one Required to be Selected:
excel vba excel-vba pivot-table conditional-formatting
edited Nov 21 at 5:52
lagom
3,39151737
asked Nov 21 at 5:22
Kuridi Sunil
11
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm trying to apply a formatting condition to a pivot field, the script below does that but applies Formatting to "Selected Cells" of the Pivot Filed and makes it a static cells range, instead of Pivot Field Values of the pivot table, where as the same can be done manually. Please look at the image attached for clear info.
ActiveSheet.PivotTables(PivotName).PivotFields("SPEC").DataRange.FormatConditions.Add Type:=xlNoBlanksCondition
With ActiveSheet.PivotTables(PivotName).PivotFields("CPR SPEC").DataRange.FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = vbBlack
End With
End with
Image Shows the Formatting Rule selected from Script and the one Required to be Selected:
excel vba excel-vba pivot-table conditional-formatting
excel vba excel-vba pivot-table conditional-formatting
edited Nov 21 at 5:52
lagom
3,39151737
asked Nov 21 at 5:22
Kuridi Sunil
11
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Nov 21 at 5:52
lagom
3,39151737
asked Nov 21 at 5:22
Kuridi Sunil
11
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Nov 21 at 5:52
lagom
3,39151737
edited Nov 21 at 5:52
lagom
3,39151737
edited Nov 21 at 5:52
lagom
3,39151737
3,39151737
asked Nov 21 at 5:22
Kuridi Sunil
11
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Nov 21 at 5:22
Kuridi Sunil
11
asked Nov 21 at 5:22
Kuridi Sunil
11
11
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Kuridi Sunil is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Kuridi Sunil is a new contributor. Be nice, and check out our Code of Conduct.
Kuridi Sunil is a new contributor. Be nice, and check out our Code of Conduct.
Kuridi Sunil is a new contributor. Be nice, and check out our Code of Conduct.
Kuridi Sunil is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405695%2fmodifyappliestorange-method-for-pivotfield%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
