I have a pivot table with 50 rows. I’ve highlighted one of the three columns selected CONDITIONAL FORMATTING – DATA BARS – GRADIENT BARS. I’ve manually entered the “Applies To” range of F8:F58. All looks good….until I refresh the pivot table a few days later and there are 5 new rows, each of which does NOT have the Gradient Data Bar format. When I go to edit the conditional format, the “Applies To” range skips these new rows.
Is there a way to dynamically change the “Applies To” range to apply to all the rows in the pivot table? Before you answer, I notice that in the “Edit Formatting Rule” dialog box there are three options:
1. Selected Cells
2. All Cells showing “Revenue” values
3. All cells showing “Revenue” values for “Name” and “Payment Type”
Unfortunately this won’t work, because there are 3 Payment Types, and I want to conditionally format each type…selecting either of these options “spreads” the format range across all Payment Types.
Ideally I’d like a non-VBA solution, but if none exists, could someone share some code or point me in the right direction, as I am a newbie when it comes to VBA.
Thanks, Dan
Is there a way to dynamically change the “Applies To” range to apply to all the rows in the pivot table? Before you answer, I notice that in the “Edit Formatting Rule” dialog box there are three options:
1. Selected Cells
2. All Cells showing “Revenue” values
3. All cells showing “Revenue” values for “Name” and “Payment Type”
Unfortunately this won’t work, because there are 3 Payment Types, and I want to conditionally format each type…selecting either of these options “spreads” the format range across all Payment Types.
Ideally I’d like a non-VBA solution, but if none exists, could someone share some code or point me in the right direction, as I am a newbie when it comes to VBA.
Thanks, Dan
