Forum Discussion

SeanC3's avatar
SeanC3
Copper Contributor
Aug 10, 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.

SeanC3's avatar
SeanC3
Copper Contributor
Aug 16, 2019
Sergei, you are brilliant! Thank you this is perfect
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 17, 2019

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.