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

K

korak30

New Member
Joined
Jun 15, 2015
Messages
18
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
I've tried something along the lines of the following
korak30 said:
Sub test()
Worksheets("Remaining Groups").Range("A1:AC17265").Select


Do While ActiveCell.Text <> Empty
Do While ActiveCell = True
ActiveCell.Offset(0, 5).Resize(2, -5).Interior.Color = RGB(Int((255 * Rnd)), Int((255 * Rnd)), Int((255 * Rnd)))
Loop
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Click to expand...

But it's not working.. anybody? Thank you so much!
 
Upvote 0
Korak30,

Have a try of this code. It will only highlight duplicates in Col A.

Code: 
Sub ColourDuplicates()
Dim Rng As Range
Dim Cel As Range
Dim Cel2 As Range
Dim Colour As Long




Set Rng = Worksheets("Sheet1").Range("A1:A" & Range("A" & 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.Interior.ColorIndex = Colour
        Cel2.Interior.ColorIndex = Colour
            Set Cel2 = Rng.FindNext(Cel2)
        
        Loop While Firstaddress <> Cel2.Address
    End If




Colour = Colour + 1


End If
Next


End Sub
 
Upvote 0
Scott,

That was wonderful!!

Could you guide me on how to modify this to highlight the entire row?

Thank you so much!
 
Upvote 0
Do you want the "entire row" or just the columns with data in them? If so, what is the last col?
 
Upvote 0
The columns with the data. I'm sorry I should have specified! The column with the repetitions to be identified is column B, but the data ranges from column A to Z.

Thanks!
 
Upvote 0
Try-

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




Set Rng = Worksheets("Sheet1").Range("A1:A" & Range("A" & 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
 
Upvote 0
Yes it still says Subscript out of Range again, unfortunately.

I've also stumbled upon another challenge.

The data I have is below:
[TABLE="width: 1217"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"][TABLE="width: 913"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]5[/TD]
[TD]1_800_flowerscom[/TD]
[TD]Legal GSO SFDC[/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]6[/TD]
[TD]1_800_flowerscom[/TD]
[TD]InterAction[/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]7[/TD]
[TD]1_automotive_group[/TD]
[TD]Legal GSO SFDC[/TD]
[TD]950 Echo Lane[/TD]
[TD]Houston[/TD]
[TD]TX[/TD]
[TD]77024-2756[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]1_automotive_group[/TD]
[TD]EPM SFDC[/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]9[/TD]
[TD]1_automotive_group[/TD]
[TD]InterAction[/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]10[/TD]
[TD]1_chambers_court_family_garden_law[/TD]
[TD]InterAction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1_chambers_court_family_garden_law[/TD]
[TD]Legal GSO SFDC[/TD]
[TD]1 Garden Court Temple[/TD]
[TD]London[/TD]
[TD][/TD]
[TD]EC4Y 9BJ[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1_chancery_lane[/TD]
[TD]Legal GSO SFDC[/TD]
[TD]1 Chancery Lane[/TD]
[TD]London[/TD]
[TD] [/TD]
[TD]WC2A 1LF[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1_chancery_lane[/TD]
[TD]InterAction[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]1_court_hare[/TD]
[TD]Legal GSO SFDC[/TD]
[TD]1 Hare Court Temple[/TD]
[TD]London[/TD]
[TD][/TD]
[TD]EC4Y 7BE[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1_court_hare[/TD]
[TD]InterAction[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1_court_pump[/TD]
[TD]InterAction[/TD]
[TD]1 Temple[/TD]
[TD]London[/TD]
[TD] [/TD]
[TD]EC4Y 7AB[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1_court_pump[/TD]
[TD]Legal GSO SFDC[/TD]
[TD]1 Pump Court Chambers Elm Court, Temple[/TD]
[TD]London[/TD]
[TD] [/TD]
[TD]EC4Y 7AH[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]1031_group_tax[/TD]
[TD]Legal GSO SFDC - Elite[/TD]
[TD][/TD]
[TD]Richmond[/TD]
[TD]VA[/TD]
[TD]23235[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]1031_group_tax[/TD]
[TD]InterAction[/TD]
[TD]100 Corporate Dr Ste 201[/TD]
[TD]Trumbull[/TD]
[TD]CT[/TD]
[TD]06611-6343[/TD]
[TD]United States[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]11_bench_kings_walk[/TD]
[TD]InterAction[/TD]
[TD]3 Park Court[/TD]
[TD]Leeds[/TD]
[TD] [/TD]
[TD]LS1 2QH[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]11_bench_kings_walk[/TD]
[TD]Legal GSO SFDC[/TD]
[TD]3 Park Court[/TD]
[TD]Leeds[/TD]
[TD] [/TD]
[TD]LS1 2QH[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]12_benchwalk_kings[/TD]
[TD]Legal GSO SFDC[/TD]
[TD]12 King's Benchwalk Temple[/TD]
[TD]London[/TD]
[TD][/TD]
[TD]EC4Y 7EL[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]12_benchwalk_kings[/TD]
[TD]InterAction[/TD]
[TD]12 King's Benchwalk Temple[/TD]
[TD]London[/TD]
[TD][/TD]
[TD]EC4Y 7EL[/TD]
[TD]United Kingdom[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I now need to match columns D, E and G, and report how many matches, 1,2 or 3 there are for each set of duplicates.

Is this something that can be done?

I'm sorry I'm asking so much, I'm pretty stressed about this. :(
 
Upvote 0
Is there anything special/different at row 106?

Is the code doing everything right up to this point?

In your post #9 can you give an example of the matching you are referring to.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Highlighting rows random colors if there duplicates one column - PART 2
Replies
4
Views
767
Trebor76
T
C
  • Question Question
Cut n Paste to same Column, but different row every time
Replies
2
Views
936
Cobra75007
C

Forum statistics

Threads
1,223,856
Messages
6,175,027
Members
452,604
Latest member
cballetti

Share this page

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