Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Cell colour changes when value occurs multiple times

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    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

    Its very easy to do with the "Conditional Format" command found on the Format Tool bar.
    For example lets say I want to turn green cells that have same data 3 times in row A

    1-select cell in row a
    2-open conditional format window from format command on tool bar
    3-Select the "formula is" from the drop down
    4- type in this formala =COUNTIF(A:A,A:A)=3
    5- click the format button and select format options

    To ADD your other two formula (i.e. for yellow and red) just hit "add" button and add =COUNTIF(A:A,A:A)=6 and=COUNTIF(A:A,A:A)=9

    After your done just copy the format into all cells in col A:A

    And your done !

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Erm, that didn't appear to do anything

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah, think I've sorted it. Thanks

  5. #5
    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

    replace A:A,A:A with A:A,A1

    etc

    before pasting down

  6. #6
    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

    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.

  7. #7
    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

    thanks Nimrod,

    but doesn't yours colour blank cells ?

    also your last condition should be >=9

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You've confused me now

    Using A:A,A:A literally, it seems to do absolutely nothing.

    Similarly for A:A,A1

    Plus, when I copy the formatting down, excel automatically increases the 1 (or any numbers used) by an increment of 1. Is there any way to stop it doing this?!

  9. #9
    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

    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.

  10. #10
    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

    my apologies...

    A:A,A3 etc and paste the format down

    (I hadn't spotted you starting in row3)



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
  •