How to Group Data using colour

[deraco123]

Hi guys

I have a spreadseet where I am pasting data which all the time change.
I would like to know if there is a way to highlight cells with same value with one colour, other same cells with second colour,3rd....10 colours would be enough i think....
Formula should work for at least 200 rows.
i.e. I have in column D Dates in 20 rows.
1-10 row are same (blue highlighted cells)
11-12 and 15 are same (yellow highlighted cells)
13-14 row are same (red highlighted cells)
16-20 row are same (red highlighted cells)

Any idea how to do it? (MSE2003)

Thanks

 

[jeffreybrown]

Not quite sure if this is what you are after, but take a look here...

http://www.contextures.com/xlCondFormat02.html

 

[jamieleeuk]

Morning,

I have a similar requirement to this where by I have 2000 dows of data and in column B I need to know if a name has been duplicated so I used conditional formatting to highlight any cells which had the same value as another in the list.

Say your 1st cell in the list is B7 and the last cell B2000

Go to the first cell (B7) in the list and go to format > conditional formatting

Change the condition to "Formula is"

type =COUNTIF(B7:B2000,B7)>1

Change the format of the cell to what you want it to be.

Say "OK" and copy the first cell (B7) then highlight the remaining cells in the list and "Paste Special > Formats"

This will only highlihgt in one colour - I don't know about several colours - but it's a start I suppose.

 

[deraco123]

Hi guys thanks for effort
Higlighting with more colour is really needed in this case.
<table style="border-collapse: collapse; width: 212px; height: 300px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 53pt;" width="71"> <col style="width: 48pt;" width="64"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 53pt;" align="right" width="71" height="20">22.11.2010</td> <td class="xl69" style="width: 48pt;" width="64">
</td> <td class="xl70" style="width: 48pt;" align="right" width="64">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">22.11.2010</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">22.11.2010</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">22.11.2010</td> <td class="xl69">
</td> <td class="xl71" align="right">222</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">22.11.2010</td> <td class="xl69">
</td> <td class="xl71" align="right">222</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">22.11.2010</td> <td class="xl69"> or</td> <td class="xl72" align="right">33</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">01.02.2009</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" align="right" height="20">02.02.2009</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">03.02.2009</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">01.02.2009</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">01.02.2009</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">01.02.2009</td> <td class="xl69">
</td> <td class="xl72" align="right">33</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" align="right" height="20">03.02.2009</td> <td class="xl69">
</td> <td class="xl72" align="right">33</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">22.11.2010</td> <td class="xl69">
</td> <td class="xl71" align="right">222</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td class="xl69">
</td> <td class="xl70" align="right">111</td> </tr> </tbody></table>I think that some vba would help here but i don't have th knowledge.

Thanks

 

[someothersoldiers]

Excel 2007 allows conditional formatting and sorting by colours, I know 2003 reads the colours as numbers and therefor it is more complicated that way

 

[scottylad2]

Excel Workbook
	H	I	J	K
1	21/01/2009	112		111
2	22/01/2009	113		112
3	22/01/2009	117		113
4	19/01/2009	117		114
5	12/01/2009	114		115
6	26/01/2009	112		116
7	15/01/2009	116		117
8	20/01/2009	114
9	19/01/2009	112
10	13/01/2009	111
11	11/01/2009	115
12	10/01/2009	111
13	22/01/2009	114
14	08/01/2009	115
15	24/01/2009	116
16	11/01/2009	115
17	14/01/2009	117
18	09/01/2009	111
19	14/01/2009	113
20	22/01/2009	113
21	11/01/2009	111
22	16/01/2009	112
23	25/01/2009	113
24	21/01/2009	112
Sheet5

Not sure if this will paste colour but i've had to cut the area i pasted as there was too many cells. Highlight the whole area of cells you want to format, dragging from the top left and go down, then with them still highlighted go to CF , new rule and select one cell from your criteria row, in my example its I1 and which gives =i1 use the dolar sign in front =$I1=my criteria which i have set in K1, s should read =$I1=K1 then format with your colour do the same for all the colours you need, i did 100 rows using 7 different colours/criteria and works no probs in 2007

 

[scottylad2]

You can then go through and sort one colour at a time till the whole columns in colour seperated colour by colour

 

[scottylad2]

Missed a dollar sign in the above..... =$I1=K$1 Always highlight =$I1 at the start of your formula, then move through each criteria in row K with the $ sign between K$1 through to K$7 in my case, but i guess you should keep trying for as many colours as you need. The fact i've not included white means there are the 7 i did plus white, and i'm sure there's a lot more before you exhaust all the colours

 

[deraco123]

is there any vba solution for this?

 

[scottylad2]

Not sure, but the formula works ok. this should bump your post back up as i'd quite like to learn that as well