Duplicates in workbook

[Hollywood691]

I'm sure this has been asked, but I can't find it. I have 2 sheets of my workbook...

sheet 1 is about 6000 rows:
12345
49318
57419
64753
35489

sheet 2 is about 1000 rows:

I want to shade the rows in sheet 1 that appear on sheet 2?

I know it's not that hard (if you know what to do), I just can't seem to get it.

any help is greatly appreciated

 

[Trebor76]

Hi Hollywood691,

Try this adapted code from Fluff:

Option Explicit
Sub ColourDuplicates()

    'Based on Fluff's code from here: _
    https://www.mrexcel.com/forum/excel-questions/1037008-find-duplicate-rows-multiple-cells-2.html#post4980293
   
   Dim Valu As String
   Dim Cl As Range
   Dim UsdCols As Long
   
   Application.ScreenUpdating = False
   
   'Create an unique array of entries in Sheet2
   UsdCols = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column
   With CreateObject("scripting.dictionary")
      For Each Cl In Sheets("Sheet2").Range("A1", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)) 'Starts from cell A1 and works dows Col. A in Sheet2. Change to suit if necessary.
         Valu = Join(Application.Transpose(Application.Transpose(Cl.Resize(, UsdCols))))
         If Not .exists(Valu) Then
            .Add Valu, Cl.Resize(, UsdCols)
         End If
      Next Cl
      'Now use the this array to check the entries in Sheet1 and if they're in the array highlight the entries in green (change to suit if necessary)
      UsdCols = Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
      For Each Cl In Sheets("Sheet1").Range("A1", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)) 'Starts from cell A1 and works dows Col. A in Sheet1. Change to suit if necessary.
         Valu = Join(Application.Transpose(Application.Transpose(Cl.Resize(, UsdCols))))
         If .exists(Valu) Then
            Cl.Resize(, UsdCols).Interior.Color = RGB(0, 255, 0)
         Else
            Cl.Resize(, UsdCols).Interior.Color = xlNone
         End If
      Next Cl
            
   End With
   
   Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Hollywood691]

thank you Robert, Gawd excel makes me feel dumb.........I have no idea where to put that or how to make it run. I've only ever used excel for simple stuff.

Brent

 

[Trebor76]

To install my macro follow these four steps:

1. Copy all my code to the clipboard
2. Press and hold the Alt key and the press F11 (Alt + F11)
3. Paste my code from step one into the blank white screen
4. From the File menu click Close and Return to Microsoft Excel

To run the code press and hold the Alt key and then press F8 (Alt + F8), click the ColourDuplicates option from the Macro dialog and then click Run

HTH

Robert

 

[Hollywood691]

To install my macro follow these four steps:

1. Copy all my code to the clipboard
2. Press and hold the Alt key and the press F11 (Alt + F11)
3. Paste my code from step one into the blank white screen
4. From the File menu click Close and Return to Microsoft Excel

To run the code press and hold the Alt key and then press F8 (Alt + F8), click the ColourDuplicates option from the Macro dialog and then click Run

HTH

Robert

Thanks Robert.......I get this: "Compile error:
Invalid outside procedure"

 

[Trebor76]

The code mustn't have copied in correctly as it worked for me

Post back with how the code exactly looks in the module and I'll try and see what the issue is.

 

[Hollywood691]

Perfect Robert, I re-tried pasting the code in and run it, works great! dont know what I had done wrong the first time. so now when I add winning numbers I just "run" the macro again right?

Thanks,
Brent

 

[Trebor76]

so now when I add winning numbers I just "run" the macro again right?

Correct. Bear in mind an Excel worksheet has 1,048,576 rows (Excel 2007 on) yet the COMBIN function tells us that there are 8,145,060 combinations to try and pick 6 numbers from 45 i.e. =COMBIN(45,6).

Good luck,

Robert

 

[Hollywood691]

that is alot of combinations lol......thats why I am working on 5/37 ,....jackpots arent that large, BUT several smaller prizes are better than nothing. Odds are more favorable too. Thank you for your help!

Brent