conditional format to highlight 3 consecutive non-zero cells

[erniepoe]

Hi all,

I've built a little table that has many rows and 10 columns (E:O), which correspond to a 10am-8pm time frame. The values in the table will either be 0 or any number from 1-4.

I would like to build some type of conditional format that would highlight the cells in a particular row if there are at least 3 consecutive that are greater than 0. So it could be cels E:G or K:N (or both), but if there are values there I would like to highlight them.

Can this be done?

Thanks so much for any help.

 

[lrobbo314]

Select all the cells in columns E:O that have your data in them and add the 3 following conditional format rules.

=AND(C2>0,D2>0,E2>0)
=AND(D2>0,E2>0,F2>0)
=AND(E2>0,F2>0,G2>0)

 

[erniepoe]

thanks, i'll give it a try! I only need to go from E:G? And not add more conditions for G:0? Appreciate it!

 

[Peter_SSs]

What is in columns C:D and columns P:Q? The answer may determine the easiest way to get your result.

 

[Marcelo Branco]

Maybe something like this

Create a named constant array
Formulas > Names Manager > New
Name: Arr_1
Refers to: ={-2,-1,0}

	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​
1​	Value1​	Value2​	Value3​	Value4​	Value5​	Value6​	Value7​	Value8​	Value9​	Value10​	Value11​
2​	1​	0​	2​	4​	3​	2​	0​	0​	1​	2​	1​
3​	2​	0​	1​	3​	1​	0​	0​	2​	2​	0​	2​
4​	1​	2​	3​	0​	0​	2​	4​	1​	1​	3​	0​

<tbody>
</tbody>

Select E2:Ox where x is the last row with data (E2:O4 in the example above) being E2 the active cell (the one not shaded after the selection)

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

Insert this formula
=OR((COLUMN(C2:E2)>=5)*(COLUMN(C2:E2)<=13)*COUNTIF(OFFSET(E2,,Arr_1,1,3),">0")=3)

pick the format you want (Fill--> yellow, for example)
Ok, Ok

Hope this helps

M.

 

[Marcelo Branco]

A more robust formula

=OR(IFERROR((COLUMN(OFFSET(E2,,N(Arr_1)))>=COLUMN($E2))*(COLUMN(OFFSET(E2,,N(Arr_1)))<=COLUMN($O2)-2)*COUNTIF(OFFSET(E2,,Arr_1,1,3),">0"),0)=3)

It works even if left-hand columns of data are deleted.

M.

 

[erniepoe]

Marcelo,

I missed this when you posted, so i hope you see this, but thank you! It works perfectly and now everybody thinks I'm a genius

Thanks again!

 

[Marcelo Branco]

Marcelo,

I missed this when you posted, so i hope you see this, but thank you! It works perfectly and now everybody thinks I'm a genius

Thanks again!

You are very welcome. Glad to help

M.

 

[Peter_SSs]

Bit late back to the thread, but piggy-backing on Marcelo's great solution, if you have access to the CONCAT function (Excel 2016 via Office 365), you could also try this
- Define the name: oSet as Refers to: ={0,1,2}
- Select E2:Ox and apply the CF formula shown below.

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O
2	1	0	2	4	3	2	0	0	1	2	1
3	2	0	1	3	1	0	0	2	2	0	2
4	1	2	3	0	0	2	4	1	1	3	0
CF consecutive (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	1. / Formula is =OR(MID(".."&CONCAT(--($E2:$O2>0)),COLUMNS($E:E)+oSet,3)="111")	Abc

</td></tr></table></td></tr></table>