Macro for cross referencing

[Mark]

Hi,

I'm looking for some advice as to how to cross reference 3 lists of genes
that I am researching.

I have these lists in a single workbook, with each list in its own
worksheet. The lists start in Cell A1 and vary in size from 337 genes in the
smaller list and upto 1489 genes in the largest.

Basically, each list refers to the genes of interest in a particular region.
I'm therefore looking for a way to cross reference these lists to show which
genes appear in all three regions.

I have used this macro below which I have adapted, however when I try to run
the macro it gives me the message "Compile Error : Next with for" and it
highlights "next" in the "End if" part of the code.

Sub FindDupes()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
str = InputBox("Type name of third sheet")
Set sht3 = Worksheets(str)
For Each cell1 In sht1.Columns(1).Cells
For Each cell2 In sht2.Columns(1).Cells
For Each cell3 In sht3.Columns(1).Cells
If cell2.Value = cell1.Value Then
cell1.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 3
If cell3.Value = cell1.Value Then
cell1.Interior.ColorIndex = 7
cell3.Interior.ColorIndex = 3
If cell2.Value = cell3.Value Then
cell3.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 7
End If
Next cell3
Next cell2
Next cell1
End Sub

I hope someone can help! Thanks.

 

[Jean-Yves]

Hello Mark,

You have to close your "If" Statement when written on more then one line.
Look for HERE in your code.

But for your need, I would use a simple countIf formula to check the other
sheets.

 

[Mike]

Hi,

I'm looking for some advice as to how to cross reference 3 lists of genes
that I am researching.

I have these lists in a single workbook, with each list in its own
worksheet. The lists start in Cell A1 and vary in size from 337 genes in the
smaller list and upto 1489 genes in the largest.

Basically, each list refers to the genes of interest in a particular region.
I'm therefore looking for a way to cross reference these lists to show which
genes appear in all three regions.

I have used this macro below which I have adapted, however when I try to run
the macro it gives me the message "Compile Error : Next with for" and it
highlights "next" in the "End if" part of the code.

Sub FindDupes()
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim sht3 As Worksheet
    Dim cell1 As Range
    Dim cell2 As Range
    Dim cell3 As Range
    Dim str As String
    str = InputBox("Type name of first sheet")
    Set sht1 = Worksheets(str)
    str = InputBox("Type name of second sheet")
    Set sht2 = Worksheets(str)
    str = InputBox("Type name of third sheet")
    Set sht3 = Worksheets(str)
    For Each cell1 In sht1.Columns(1).Cells
        For Each cell2 In sht2.Columns(1).Cells
            For Each cell3 In sht3.Columns(1).Cells
            If cell2.Value = cell1.Value Then
                cell1.Interior.ColorIndex = 5
                cell2.Interior.ColorIndex = 3
            If cell3.Value = cell1.Value Then
                cell1.Interior.ColorIndex = 7
                cell3.Interior.ColorIndex = 3
            If cell2.Value = cell3.Value Then
                cell3.Interior.ColorIndex = 5
                cell2.Interior.ColorIndex = 7
            End If
        Next cell3
    Next cell2
Next cell1
    End Sub

I hope someone can help! Thanks.

If you have three IF statements you will need three End If
statements. Otherwise, the bracketing is not what you think it is.

--Mike Jr.

 

[Mark]

Do you mean something like this:

Sub FindDupes()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
str = InputBox("Type name of third sheet")
Set sht3 = Worksheets(str)
For Each cell1 In sht1.Columns(1).Cells
For Each cell2 In sht2.Columns(1).Cells
For Each cell3 In sht3.Columns(1).Cells
If cell2.Value = cell1.Value Then
cell1.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 3
If cell3.Value = cell1.Value Then
cell1.Interior.ColorIndex = 7
cell3.Interior.ColorIndex = 3
If cell2.Value = cell3.Value Then
cell3.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 7
End If
Next cell1
End If
Next cell2
End If
Next cell3
End Sub

I quite new to this, sorry if i'm making obvious errors!

Thanks

 

[Jean-Yves]

No,like this :

Sub FindDupes()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
str = InputBox("Type name of third sheet")
Set sht3 = Worksheets(str)
For Each cell1 In sht1.Columns(1).Cells
For Each cell2 In sht2.Columns(1).Cells
For Each cell3 In sht3.Columns(1).Cells
If cell2.Value = cell1.Value Then
cell1.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 3
End if
If cell3.Value = cell1.Value Then
cell1.Interior.ColorIndex = 7
cell3.Interior.ColorIndex = 3
End if
If cell2.Value = cell3.Value Then
cell3.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 7
End If
Next cell1
Next cell2
Next cell3
End Sub

 

[Mark]

Sorry, I meant:

Sub FindDupes()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim cell3 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)
str = InputBox("Type name of third sheet")
Set sht3 = Worksheets(str)
For Each cell1 In sht1.Columns(1).Cells
For Each cell2 In sht2.Columns(1).Cells
For Each cell3 In sht3.Columns(1).Cells
If cell2.Value = cell1.Value Then
cell1.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 3 End If
If cell3.Value = cell1.Value Then
cell1.Interior.ColorIndex = 7
cell3.Interior.ColorIndex = 3 End If
If cell2.Value = cell3.Value Then
cell3.Interior.ColorIndex = 5
cell2.Interior.ColorIndex = 7
End If
Next cell3
Next cell2
Next cell1
End Sub

 

[Mark]

Ok, So I've got the code working. But it keeps crashing! Any suggestions?

 

[ryguy7272]

This does a nice job of comparing two sheets:
Sub FindDupes() 'assuming both sheets are in same book and book is open
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim cell1 As Range
Dim cell2 As Range
Dim str As String
str = InputBox("Type name of first sheet")
Set sht1 = Worksheets(str)
str = InputBox("Type name of second sheet")
Set sht2 = Worksheets(str)


sht1.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht1 = ActiveCell.Row

sht2.Activate
sht2.Range("A65536").End(xlDown).Activate
Selection.End(xlUp).Activate
LastRowSht2 = ActiveCell.Row

sht1.Activate
For rowSht1 = 1 To LastRowSht1
If sht1.Cells(rowSht1, 1) = "" Then Exit Sub
For rowSht2 = 1 To LastRowSht2
If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value
Then
sht1.Cells(rowSht1, 1).Interior.ColorIndex = 3
sht2.Cells(rowSht2, 1).Interior.ColorIndex = 3

End If
Next
Next
sht1.Cells(1, 1).Select
End Sub


Regards,
Ryan---

PS, same answer in your other post too...not sure which you will look at...

 

[Mark]

Hi, Thanks for the help. However, I'm getting a syntax error message at the
line:

If sht1.Cells(rowSht1, 1).Value = sht2.Cells(rowSht2, 1).Value
Then

Not sure why it doing that? Any ideas?

Thanks.