Conditional Formatting if 6 or more cells in a range are not blank

**marquella** · 01-02-2011, 11:21 PM

I'm trying to create a work schedule so that if 6 or more shifts are being scheduled, that the cell will highlight. I was able to set up the formula in the 7th cell by just doing a not(isblank(ref)+isblank(ref) etc. but I'm stumped about creating a formula that highlights a cell if 6 or more cells in a range are not blank. Any help would be greatly appreciated! TIA!

**DonkeyOte** · 01-03-2011, 09:01 AM

Are the cells truly blank (devoid of all content) or could they contain Formula Nulls ? [""]

Considering above dilemma and assuming Nulls should equate to Blanks for purposes of this exercise then it might be better to use a rule along the lines of:

Please Login or Register  to view this content.

Of course if your range is literally 6 columns then

Please Login or Register  to view this content.

should suffice (ie just test for presence of at least 1 blank/null)

**marquella** · 01-03-2011, 01:41 PM

The cells would never have null values in them, I just want to alert the person who is doing the scheduling that they are scheduling to many shifts for that week (more than 6). The values that are put into the cells range from 1 to 22 or there may be text in it, which is what section the person would be working in. I will have some other cells in the range with conditional formatting as well but again, the cell itself would never be null.

**DonkeyOte** · 01-03-2011, 01:46 PM

If you don't have to concern yourself with Nulls than COUNTA alone should suffice.

**marquella** · 01-03-2011, 03:08 PM

Thanks, it worked! I'm about to mark this solved but now that I'm applying another conditional format to the cell, I've encountered a conflict. I have it set so that the above condition (6 or more cells are filled) will color and bold the font. I've set up additional formats so that if the numbers 2, 4 or 12 are entered, the cell will highlight. My issue is that if I make the cell highlight rule the top one, then it won't bold and color the font and if I make the font rule the top one, then the cell won't highlight. Is there any way I can resolve this conflict? Thanks again!