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" wrote:

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

"Mike" wrote:

On Apr 17, 9:03 am, Mark wrote:
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.