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}


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Value1​

[/TD]
[TD]

Value2​

[/TD]
[TD]

Value3​

[/TD]
[TD]

Value4​

[/TD]
[TD]

Value5​

[/TD]
[TD]

Value6​

[/TD]
[TD]

Value7​

[/TD]
[TD]

Value8​

[/TD]
[TD]

Value9​

[/TD]
[TD]

Value10​

[/TD]
[TD]

Value11​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD="bgcolor: #FFFF00"]

4​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD="bgcolor: #FFFF00"]

2​

[/TD]
[TD="bgcolor: #FFFF00"]

4​

[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD="bgcolor: #FFFF00"]

1​

[/TD]
[TD="bgcolor: #FFFF00"]

3​

[/TD]
[TD]

0​

[/TD]
[/TR]
</tbody>[/TABLE]



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>