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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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 !
 
Upvote 0
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.
 
Upvote 0
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?!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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