Highlighting rows with random colors if there are duplicates in one column

[korak30]

Hello, I'd like to highlight rows with random colors if there are duplicates (anywhere between 3-10) in one of the columns. My data set looks like this:

[TABLE="width: 752"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]1_800_flowerscom[/TD]
[TD]1 Old Country Rd Ste 500[/TD]
[TD]Carle Place[/TD]
[TD]NY[/TD]
[TD]11514-1847[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]1_800_flowerscom[/TD]
[TD]1 Old Country Rd Ste 500[/TD]
[TD]Carle Place[/TD]
[TD]NY[/TD]
[TD]11514-1847[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]1_automotive_group[/TD]
[TD]950 Echo Lane[/TD]
[TD]Houston[/TD]
[TD]TX[/TD]
[TD]77024-2756[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]1_automotive_group[/TD]
[TD]800 Gessner Rd Ste 500[/TD]
[TD]Houston[/TD]
[TD]TX[/TD]
[TD]77024-4498[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]1_automotive_group[/TD]
[TD]950 Echo Lane[/TD]
[TD]Houston[/TD]
[TD]TX[/TD]
[TD]77024-2756[/TD]
[TD]United States of America[/TD]
[/TR]
[TR]
[TD]1_chambers_court_family_garden_law[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1_chambers_court_family_garden_law[/TD]
[TD]1 Garden Court Temple[/TD]
[TD]London[/TD]
[TD][/TD]
[TD]EC4Y 9BJ[/TD]
[TD]United Kingdom[/TD]
[/TR]
</tbody>[/TABLE]

Basically I'd like to highlight the rows, for duplicate values in column A, with a unique color. The reason I need a random color is because there are 17000 rows.

I've tried some basic conditional formatting but that's not working.

Any help is much appreciated. Thanks!

 

[korak30]

Nothing different in Row 106, really. Actually the code from #8 does not run at all and gives a subscript out of range error before starting any coloring.

So the matching is something like this:

I'm trying to produce a column to the right of the data that has the number of matches.
So H5 and H6 should say "3"
H7, H8 and H9 should say "1" because only "Houston" matches in all 3
H10 and H11 should give "0"
.
.
.
H20 and H21 should say "3"

This is based on comparing columns D, E, and G within each set of duplicates (and there can be none or many duplicates)

 

[farmerscott]

Nothing different in Row 106, really. Actually the code from #8 does not run at all and gives a subscript out of range error before starting any coloring.

OK I can see an error in that code. Try-

Sub ColourDuplicates2()
Dim Rng As Range
Dim Cel As Range
Dim Cel2 As Range
Dim Colour As Long




Set Rng = Worksheets("Sheet1").Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
Rng.Interior.ColorIndex = xlNone
Colour = 6


For Each Cel In Rng

If WorksheetFunction.CountIf(Rng, Cel) > 1 And Cel.Interior.ColorIndex = xlNone Then
Set Cel2 = Rng.Find(Cel.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchDirection:=xlNext)
    If Not Cel2 Is Nothing Then
        Firstaddress = Cel2.Address
        Do
        Cel.Offset(0,-1).Resize(1,26).Interior.ColorIndex = Colour
        Cel2.Offset(0,-1).Resize(1,26).Interior.ColorIndex = Colour

           Set Cel2 = Rng.FindNext(Cel2)
        
        Loop While Firstaddress <> Cel2.Address
    End If




Colour = Colour + 1


End If
Next


End Sub

As a matter of interest (RE: row 106) step through this code (using F8) and see what the value of lr (by hovering over it), is

Sub lr()

dim lr as long

lr= Range("B" & Rows.Count).End(xlUp).Row

End sub

 

[korak30]

Hi, the code stops at A105 again with an runtime error 9 supscript out of range error. The value of lr is 0.

 

[korak30]

Oh! There might be an issue but I'm not sure. The values in cell B107 and B108, just below where the code stops, don't have any underscores. I'm not sure if this helps though.

 

[farmerscott]

When you say the code stops at row 106, I am assuming the duplicates are highlighted in colour (above that point)?

Can you post a screenshot of rows 100 to 110 after the code has been run? Check out here for instructions- http://www.mrexcel.com/forum/about-board/508133-attachments.html


With matching your duplicates I am assuming that USA=United States=United States of America????

 

[korak30]

When you say the code stops at row 106, I am assuming the duplicates are highlighted in colour (above that point)?

Can you post a screenshot of rows 100 to 110 after the code has been run? Check out here for instructions- http://www.mrexcel.com/forum/about-board/508133-attachments.html

Excel Workbook
	A	B	I	J	K	L	M	N	O	T	W	X	Y	Z
99	98	66_phillips	EPM SFDC	3010 Briarpark Dr	Houston	TX	77042-3706	USA	078423447	05656	Master
100	99	66_phillips	InterAction	3010 Briarpark Dr	Houston	TX	77042-3706	United States	078423447	05656	Merge
101	100	7_bedford_row	Legal GSO SFDC	7 Bedford Row	London		WC1R 4BU	United Kingdom			Merge
102	101	7_bedford_row	InterAction	6-7 Bedford Row	London		WC1R 4BS	United Kingdom	216015391		Master
103	102	7_eleven	InterAction	Ste 1000	Dallas	TX	75201	United States	616913935		Merge
104	103	7_eleven	Legal GSO SFDC	1722 Routh St Ste 1000	Dallas	TX	75201-2506	United States	007347602		Master
105	104	722_investments	Legal GSO SFDC	244 Fifth Ave #2272	New York	NY	10001				Merge
106	105	722_investments	InterAction	244 Fifth Ave Suite 2272	New York	NY	10001	United States of America			Master
107	106	7kbw	InterAction	7 Temple King's Bench Walk	London		EC4Y 7DS	United Kingdom	423875595		Master
108	107	7kbw	Legal GSO SFDC	7 King's Bench Walk	London		EC4Y 7DS	United Kingdom			Merge
109	108	9_buildings_stone	InterAction	Lincoln's Inn Lde: 314 Chancery Lane	London	Greater London	WC2A 3NN	United Kingdom			Merge
110	109	9_buildings_stone	Legal GSO SFDC	Lincoln's Inn Lde: 314 Chancery Lane	London		WC2A 3NN	United Kingdom			Master
Remaining Groups

With matching your duplicates I am assuming that USA=United States=United States of America????

No, I'm just looking to match columns J,K, and M.

Thank you so much!

 

[farmerscott]

Do rows 105 and 106 need to be there, since they "look" blank? Can we delete them?

Are all the entries in groups/sorted or are they randomly down the page?

 

[farmerscott]

I tested the code on some mock-up data and it skipped the 'true' blank rows (ie was not coloured), based on Col B.

However, when I put a space in visually blank cells (ie not 'truely' blank) it colours the rows. Hence your cells B105 and B106 have spaces in them. This can also be tested using =LEN(B105). If it does not equal 0 then there are spaces within the cells that we can not see.

But this does not solve the question as to why the code does not proceed past the rows in question.

Hmmmmm......

 

[korak30]

Do rows 105 and 106 need to be there, since they "look" blank? Can we delete them?

Are all the entries in groups/sorted or are they randomly down the page?

They actually are populated, but just with the same color as the shading is. I should have changed that, but for some reason my screenshot had the text a darker shade than the color, so the text was visible for rows 105 and 106.

They are all in groups, or at least should be, because I sorted them.

 

[korak30]

Excel Workbook
	A	B	I	J	K	L	M	N	O	T	W	X	Y	Z
101	100	7_bedford_row	Legal GSO SFDC	7 Bedford Row	London		WC1R 4BU	United Kingdom			Merge
102	101	7_bedford_row	InterAction	6-7 Bedford Row	London		WC1R 4BS	United Kingdom	216015391		Master
103	102	7_eleven	InterAction	Ste 1000	Dallas	TX	75201	United States	616913935		Merge
104	103	7_eleven	Legal GSO SFDC	1722 Routh St Ste 1000	Dallas	TX	75201-2506	United States	007347602		Master
105	104	722_investments	Legal GSO SFDC	244 Fifth Ave #2272	New York	NY	10001				Merge
106	105	722_investments	InterAction	244 Fifth Ave Suite 2272	New York	NY	10001	United States of America			Master
107	106	7kbw	InterAction	7 Temple King's Bench Walk	London		EC4Y 7DS	United Kingdom	423875595		Master
108	107	7kbw	Legal GSO SFDC	7 King's Bench Walk	London		EC4Y 7DS	United Kingdom			Merge
109	108	9_buildings_stone	InterAction	Lincoln's Inn Lde: 314 Chancery Lane	London	Greater London	WC2A 3NN	United Kingdom			Merge
110	109	9_buildings_stone	Legal GSO SFDC	Lincoln's Inn Lde: 314 Chancery Lane	London		WC2A 3NN	United Kingdom			Master
Remaining Groups