Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Cell colour changes when value occurs multiple times

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-05 09:30, Nimrod wrote:
    No Chris there is absolutely no need to change the A:A to A1 . This only adds alot of extra work to achieve the same results.
    Just curious:

    What is a lot of extra work with

    =COUNTIF(A:A,A1)=n

    compared to

    =COUNTIF(A:A,A1)=n ?

    Aladin

  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 09:45, Nimrod wrote:
    no the A:A formula will not color any blank cells. The Second A:A only points to the emidiate cell . Any time you put a range in the formula's where it is expecting a specific cell it only points to the emediate cell.
    Try typing a formula in a cell using the range method and then click on the formula to trace the pointers. You will see that this is true.
    Nimrod,

    I really can't get yours to work

    are you testing it in front of you ?

  3. #13
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Juggernaut:
    Please cut an paste to here what your typeing into the formula window of the Conditional format window.
    I'm typing the following and having no problems.
    =COUNTIF(A:A,A:A)=3

    If your having problems simply your rules by just starting with one formula.
    Are you putting the data in A:A as well ?

  4. #14
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Juggernaut:
    Maybe start with just one formula and get that working first i.e.
    =COUNTIF(A:A,A:A)=3

    Make sure it is identical to above , including both "=" signs.
    Make sure you have the same data in exactly three cells in row A:A. ... or change the formula to =COUNTIF(A:A,A:A)>3 if you don't want it to be for exactly 3

  5. #15
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-05 08:50, Juggernaut wrote:
    I've been racking my brains for about an hour trying to write a macro do this, and have failed so far.

    I have an excel sheet with many horizontal lines of data.

    What I want to do is check all the values in Column A from A3 to A10000.
    Where there are 3 values the same (values will be text - a person's name), those cells turn green
    Where there are 6 values the same, those cells turn yellow
    Where there are 9 values or more the same, those cells turn red.

    Any suggestions on the code required to do this?

    [ This Message was edited by: Juggernaut on 2002-05-05 08:52 ]
    Suppose we have in A3:A30 the sample:

    {"and";
    "and";
    "and";
    "bert";
    "bert";
    "bert";
    "bert";
    "bert";
    "bert";
    "cora";
    "cora";
    "cora";
    "cora";
    "cora";
    "cora";
    "cora";
    "and";
    "and";
    "and";
    "and";
    "and";
    "and";
    "and";
    "and";
    "and";
    "goku";
    "goku";
    "goku"}

    Care to indicate the color each cell must have?

  6. #16
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes Chris I tested it before I sent any suggestions.
    Condition 1
    =COUNTIF(A:A,A:A)=3
    condition 2
    =COUNTIF(A:A,A:A)=6
    condition 3
    =COUNTIF(A:A,A:A)=9

    The solution is working without any problems

  7. #17
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 10:00, Nimrod wrote:
    Yes Chris I tested it before I sent any suggestions.
    Condition 1
    =COUNTIF(A:A,A:A)=3
    condition 2
    =COUNTIF(A:A,A:A)=6
    condition 3
    =COUNTIF(A:A,A:A)=9

    The solution is working without any problems
    I believe you

    Condition 1
    =COUNTIF(A:A,A3)=3
    condition 2
    =COUNTIF(A:A,A3)=6
    condition 3
    =COUNTIF(A:A,A3)>=9

    entered in A3 and pasted down works perfectly on mine, whereas your's doesn't at all

    very curious




    EDIT - XL97 here if that makes any difference

    [ This Message was edited by: Chris Davison on 2002-05-05 10:12 ]

  8. #18
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    xl2000 here

  9. #19
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well Chris I don't mean to be rude but I'm expected elsewhere very soon. So I'm off. Cheers for now!

  10. #20
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no worries,

    I'll have a hunt around to see if it's anything to do with differences in excel versions

    have fun !

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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