Conditional format 5 or more consecutive "No" (or >4 consecutive Yes) in a range

Hi,

first post: I need to analyze a serie of tests (F5:F1000).
Is it possible with conditional format to highlight in green the series of at least 5 consecutive "yes" and in red the series of at least 5 consecutive "No" ?

The range has no blank cells and contains "Yes" "No" "Nul".

A big thank you

Hi Lalla

Welcome to the Forum ...

Maybe not the most elegant way, however ...

Highlight F6:F75
Condition 1 (Green)
Formula is:=

Please Login or Register  to view this content.

Condition 2 (Red)
Formula is:=

Please Login or Register  to view this content.

I would adopt Marcol's approach though perhaps modify to an embedded IF approach so as to stop calculating once true ?

Another alternative might be:

Please Login or Register  to view this content.

change "yes" to "no" for opposite rule - but obviously far less intuitive - and less efficient I would say.

Hi, a probably naif or less efficient solution.

In F10 to conditionally format, "the formula is" (I'm Excel 2000 user) and to be copied down and up with paste special format.

Green yes

Please Login or Register  to view this content.

Second rule:

Red no

Please Login or Register  to view this content.

Regards

Saluti (even if historically Siena and Firenze do not get along)

Interesting solution CANAPONE

The OP states

The range has no blank cells and contains "Yes" "No" "Nul".

... but if there are blanks, anomalies can occur with your formulae, so it is perhaps less robust.

This is not a criticism, just an observation.

See this modified workbook

I like that approach too.

Appending with an appropriate delimiter should resolve that, e.g:

Please Login or Register  to view this content.