Thanks:  0
Likes:  0

# Thread: Cell colour changes when value occurs multiple times

1. 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 ?

2. 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. 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. 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. 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. 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. 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. xl2000 here

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

10. no worries,

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

have fun !

## User Tag List

#### Posting Permissions

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