Forum Discussion

SeanC3's avatar
SeanC3
Copper Contributor
Aug 11, 2019
Solved

Conditional format cells only that contain a sequence of blanks

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 hi...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 16, 2019

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 17, 2019

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.

 

 

 

Resources