Conditional formatting formula to highlight groups in columns

I have asked this question on another excel forum and although it has 80 views I haven't had one reply either affirming that it is possible or letting me know that it can't be done.

I am therefore hoping someone on this forum will have some idea as to the possibility of this being achievable or otherwise.

So on to my problem.

I have a table with a number of columns and each column row is populated by either a 1 (one) or a 0 (zero).


Each column could have a number of 1's or 0's in successive row cells and each column will vary.


What I would like to do is apply a conditional formatting formula to highlight a number of 0's in a column.


If for instance there are five 0's in succession it would highlight that group of cells in the column yellow.


If there are 10 0's in succession, it would highlight that group of cells in the column red and so on.


I think it might involve using the FREQUENCY command but I am not sure how to go about applying it.


Everything I have tried with my limited knowledge of Excel formulas has failed even after reading everything I could find.


Is this actually possible to do what I am asking?


Thanks
Bill

Hi, attached an example.

5 or more zeros yellow, from ten red.

I'm using as rule for f.c. formula like the following in A11

=AND(A11=0;ISNUMBER(SEARCH(REPT(A11;10);CONCATENATE(A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16;A17;A18;A19;A20))))

Hope it's a little help

Thanks for the fast reply Canapone

Canapone said:

Hi, attached an example.

5 or more zeros yellow, from ten red.

I'm using as rule for f.c. formula like the following in A11

=AND(A11=0;ISNUMBER(SEARCH(REPT(A11;10);CONCATENATE(A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16;A17;A18;A19;A20))))

Hope it's a little help

Click to expand...

Hi Canapone and thanks for your help.

I have had a look at your formulas and they definitely work but what I don't understand is why there are so many different ones for the one column. Is there some way to apply just one conditional format for Yellow to the entire column and another conditional format for Red for the entire column?

I don't understand why there is one format for $A2:$A9 and then another for $A10 and yet another for $A11:$A75.

I also don't understand why in the format for $A11:$A75 the formula contains after CONCATENATE (A6,A7,A8,A9,A10,A11,A12,A13,A14,A15) and why this couldn't just be (A1:A75).

Is there a reason for not using just two formats (for the two colours) for the whole column?

Thanks again for your help.

Regards
Bill

Hi,

I set the two rules once in cell A11

=AND(A11=0,ISNUMBER(SEARCH(REPT(A11,10),CONCATENATE(A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20))))

=AND(A11=0,ISNUMBER(SEARCH(REPT(A11,5),CONCATENATE(A7,A8,A9,A10,A11,A12,A13,A14,A15))))

Then I' ve copied A11 (control+C) and using paste speciall formats I've copied the conditional format over the range I have to control (in the example A2100).

You can also substitute SEARCH with FIND: same formula.
Unfortunately you cannot concatenate ranges. You could use & in order to create the big string where SEARCH is looking for 00000 or 0000000000

Here I've used same formula here:

http://www.excelforum.com/excel-gen...ive-yes-in-a-range.html?p=2774152#post2774152

Hope that helps