Results 1 to 4 of 4

Thread: Conditional formatting formula to highlight groups in columns

  1. #1

    Conditional formatting formula to highlight groups in columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    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
    Attached Files Attached Files

  3. #3

    Thanks for the fast reply Canapone

    Quote Originally Posted by Canapone View Post
    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
    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

  4. #4
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    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 A2:P100).

    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-gene...52#post2774152

    Hope that helps
    Attached Files Attached Files
    Last edited by Canapone; 2012-12-29 at 05:58 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •