Cell colour changes when value occurs multiple times

Juggernaut · May 5th, 2002, 09:50 AM

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 ]

Nimrod · May 5th, 2002, 10:07 AM

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 !

Juggernaut · May 5th, 2002, 10:19 AM

Erm, that didn't appear to do anything

Juggernaut · May 5th, 2002, 10:21 AM

Ah, think I've sorted it. Thanks

Chris Davison · May 5th, 2002, 10:26 AM

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

etc

before pasting down

Nimrod · May 5th, 2002, 10:30 AM

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.

Chris Davison · May 5th, 2002, 10:36 AM

thanks Nimrod,

but doesn't yours colour blank cells ?

also your last condition should be >=9

Juggernaut · May 5th, 2002, 10:41 AM

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?!

Nimrod · May 5th, 2002, 10:45 AM

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.

Chris Davison · May 5th, 2002, 10:46 AM

my apologies...

A:A,A3 etc and paste the format down

(I hadn't spotted you starting in row3)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 5th, 2002, 09:50 AM	#1
Juggernaut New Member Join Date: May 2002 Posts: 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 ]

May 5th, 2002, 10:07 AM	#2
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	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 !

May 5th, 2002, 10:19 AM	#3
Juggernaut New Member Join Date: May 2002 Posts: 6	Erm, that didn't appear to do anything

May 5th, 2002, 10:21 AM	#4
Juggernaut New Member Join Date: May 2002 Posts: 6	Ah, think I've sorted it. Thanks

May 5th, 2002, 10:26 AM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	replace A:A,A:A with A:A,A1 etc before pasting down

May 5th, 2002, 10:30 AM	#6
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	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.

May 5th, 2002, 10:36 AM	#7
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	thanks Nimrod, but doesn't yours colour blank cells ? also your last condition should be >=9

May 5th, 2002, 10:41 AM	#8
Juggernaut New Member Join Date: May 2002 Posts: 6	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?!

May 5th, 2002, 10:45 AM	#9
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	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.

May 5th, 2002, 10:46 AM	#10
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	my apologies... A:A,A3 etc and paste the format down (I hadn't spotted you starting in row3)