Conditional Formatting - Highlight First 6 non-zero cells in range

[dohsan]

Hi Everyone,


I've been trying to highlight the first 6 non-zero cells in a range via conditional formatting.


I have a test row from A1:L1 with the following values in:

0,0,0,1,2,3,4,5,0,7,8,0


The cells that should be highlighted are those with the values 1,2,3,4,5,7


I was successful in highlighting all cells greater than 0 with the below formula:


{=OR(COLUMN(A1)=IF($A$1:$L$1>0,COLUMN($A$1:$L$1)))}


I then modified it using the SMALL function to return the first 6


{=OR(COLUMN(A1)=SMALL(IF($A$1:$L$1>0,COLUMN($A$1:$L$1)),{1,2,3,4,5,6}))}


This worked on the sheet itself, but conditional formatting won't take static Arrays, so I modified it further by adding in a named range:


{=OR(COLUMN(A1)=SMALL(IF($A$1:$L$1>0,COLUMN($A$1:$L$1)),test))}


The named range "test" was setup as ={1,2,3,4,5,6}


Again, this worked on the sheet itself, but not with the conditional formats.


Any ideas how to get around this?

 

[Luke M]

With A1:L1 selected, and A1 being the active cell, CF formula is:

=AND(A1>0,COUNTIF($A$1:A1,">0")<=6)

 

[dohsan]

Wow, I must say it didn't occur to me to anchor the range like that with COUNTIF.


Very elegant solution, thanks very much.

 

[Luke M]

Always glad to help. =)

 

[keymaster]

Very elegant solution Luke