Conditional format cells only that contain a sequence of blanks

[SeanC3]

Hi all, I need some help trying to nut out a conditional formating formula. I need to only highlight cells that contain 3 or more blanks in sequence in a row (range E2:O2) The desired outcome is to highlight the cell A2 as row A has 3 or more blanks in a row. Following this A6 will highlight but the remaining A cells will remain not highlighted. If any advice can be given for the formula to use it would be amazing! Thanks E.g. below

[#2]

UPDATE: incorrect information below: "The desired outcome is to highlight the cell A2 as row A has 3 or more blanks in a row. " Row 2 is the correct reference. Thanks

[#3]

@SeanC3 

You may apply to your range in column A, starting from A2, conditional formatting rule with formula

=COUNTBLANK($E2:$O2)>=3

[#4]

Hi Sergei, This formula is identifying rows with the required amount of blank cells, but is there a way to identify blanks in a sequence? Say row 2 has 4 blanks cells (I2:L2) but row 4 only has a maximum is 2 blanks in sequence, I need to identify only the rows that have 4 or more blanks in sequence. Is this possible? Thanks for your help.

[#5 · Accepted solution]

@SeanC3 

To count max number of sequential blanks you may use

=MAX(FREQUENCY(IF(($E2:$O2=""),COLUMN($E2:$O2)),IF($E2:$O2<>"",COLUMN($E2:$O2))))

see in Column P attached. In general it shall be array formula (Ctrl+Shift+Enter), but I'm on Dynamic Array Excel now and can't test. Column A is highlighted using this formula.

[#6]

Sergei, you are brilliant! Thank you this is perfect

[#7]

@SeanC3 

This involves a bit of lateral thinking to transform your problem into a form that is easy to solve.  Given the range, I defined a Named Formula 'signature' using Name Manager to refer to

= CONCAT( N(ISBLANK(range)) )

That gives a binary string with 1 for a blank cell and 0 otherwise.

The next step was to search the string for the occurrence of "111" using the named formula 'format?' which refers to

= IFERROR( SEARCH("111", signature ), 0 )

The conditional format then is based upon

= format?

A side benefit is that

= INDEX( range, format? )

is the first cell of the three or more consecutive blank cells.

[#8]

@SeanC3 

You are welcome. Forgot to say, array formula (CSE) if only you use it for the calculation in the cell. Within the rule you may use it as regular one, Conditional Formatting is smart enough to recognise array calculation.

[#9]

@Peter Bartholomew 

Peter, great idea with binary string