Finding Duplicates

[Maccers93]

Hey guys,

Just wondering, I keep getting error 91 when running the below:

Sub HighlightCellIfValueExistsinAnotherColumn()
    Dim ws As Worksheet
    Dim i As Long
    Dim j As Long
    Dim LastRow As Long
    Dim LastRow2 As Long

LastRow = Cells(Rows.Count, "Q").End(xlUp).Row
LastRow2 = Cells(Rows.Count, "A").End(xlUp).Row

For j = 2 To LastRow2
    For i = 2 To LastRow
    If ws.Range("A" & i).Value <> ws.Range("Q" & j).Value Then GoTo NextInteration
        If ws.Range("A" & i).Value = ws.Range("Q" & j).Value Then
            ws.Range("A" & i).Interior.ColorIndex = 4 'green
            ws.Range("Q" & j).Interior.ColorIndex = 4 'green
            GoTo NextInteration 'otherwise next interation
        End If
NextInteration:
    Next i
Next j
End Sub

Anyone lend a hand please I would greatly appreciate it?

Thanks in advance!

 

[Fluff]

Whilst you have declared ws as worksheet, you haven't actually set it to anything.

 

[Maccers93]

Whilst you have declared ws as worksheet, you haven't actually set it to anything.

Thanks for your reply @Fluff, how do I declare what it is, I know it's Set ws = (but to what?)

 

[Fluff]

(but to what?)

What sheet do you want it to work on?

 

[Maccers93]

What sheet do you want it to work on?

Sheet1?

 

[Fluff]

How about

Sub HighlightCellIfValueExistsinAnotherColumn()
    Dim ws As Worksheet
    Dim i As Long
    Dim j As Long
    Dim LastRow As Long
    Dim LastRow2 As Long

Set ws = Sheets("Sheet1")
LastRow = ws.Cells(Rows.Count, "Q").End(xlUp).Row
LastRow2 = ws.Cells(Rows.Count, "A").End(xlUp).Row

For j = 2 To LastRow2
    For i = 2 To LastRow
    If ws.Range("A" & i).Value <> ws.Range("Q" & j).Value Then GoTo NextInteration
        If ws.Range("A" & i).Value = ws.Range("Q" & j).Value Then
            ws.Range("A" & i).Interior.ColorIndex = 4 'green
            ws.Range("Q" & j).Interior.ColorIndex = 4 'green
            GoTo NextInteration 'otherwise next interation
        End If
NextInteration:
    Next i
Next j
End Sub

 

[Maccers93]

How about

Sub HighlightCellIfValueExistsinAnotherColumn()
    Dim ws As Worksheet
    Dim i As Long
    Dim j As Long
    Dim LastRow As Long
    Dim LastRow2 As Long

Set ws = Sheets("Sheet1")
LastRow = ws.Cells(Rows.Count, "Q").End(xlUp).Row
LastRow2 = ws.Cells(Rows.Count, "A").End(xlUp).Row

For j = 2 To LastRow2
    For i = 2 To LastRow
    If ws.Range("A" & i).Value <> ws.Range("Q" & j).Value Then GoTo NextInteration
        If ws.Range("A" & i).Value = ws.Range("Q" & j).Value Then
            ws.Range("A" & i).Interior.ColorIndex = 4 'green
            ws.Range("Q" & j).Interior.ColorIndex = 4 'green
            GoTo NextInteration 'otherwise next interation
        End If
NextInteration:
    Next i
Next j
End Sub

The simplest things would drive me mad, thank you for your assistance!

 

[Fluff]

You're welcome & thanks for the feedback.