Cell colour changes when value occurs multiple times

Juggernaut

New Member
Joined
May 4, 2002
Messages
6
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
 
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
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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 ?
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Well Chris I don't mean to be rude but I'm expected elsewhere very soon. So I'm off. Cheers for now!
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top