Conditional formatting with multiple conditions not working

megera716

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows
I have a Pivot table in M2:O128. When the Pivot table fields are collapsed, I want to bold the cells on that row (which is the total of the rows beneath it) from columns P:V

My conditional formatting formula is =AND(ISBLANK($N3:$N128),ISNUMBER($O3:$O128)) and I've applied it to $P$3:$V$128.

When the Pivot table is collapsed, N is blank, but there are numbers in O, so I want the formatting. When I expand the field, the formatting in P:V goes away even though the conditions are still true -- N is still blank and O still contains a number (and my "applies to" range is big enough to cover the entire table when all fields are expanded)

My formula used to just be ISBLANK($N3:$N128) and it worked perfectly. But I added the ISNUMBER because the conditional formatting was applying to all the now-empty rows when the Pivot is fully collapsed and it was a little bit blinding.

What I'm getting:
conditional formatting2.PNG


What I want:
conditional formatting1.PNG
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,312
Select P3:V128 and put in this Conditional Formatting formula.
Excel Formula:
=AND(ISBLANK($N3),ISNUMBER($O3))

Excel will automatically adjust the cell references for each row.

The ISBLANK and ISNUMBER only work on a single value and not a range (unless used in an array formula which is not necessary in this case).
 

megera716

Board Regular
Joined
Jan 3, 2013
Messages
103
Office Version
  1. 365
Platform
  1. Windows
Select P3:V128 and put in this Conditional Formatting formula.
Excel Formula:
=AND(ISBLANK($N3),ISNUMBER($O3))

Excel will automatically adjust the cell references for each row.

The ISBLANK and ISNUMBER only work on a single value and not a range (unless used in an array formula which is not necessary in this case).

Sorry, I hadn't had time to return to my project or try this formula earlier. Thank you, it worked perfectly!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,663
Messages
5,549,290
Members
410,908
Latest member
Allen P
Top