ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro for cross referencing (https://www.excelbanter.com/excel-programming/409519-macro-cross-referencing.html)

Mark
Macro for cross referencing
 
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[_4_]
Macro for cross referencing
 
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.

--
Regards

Jean-Yves Tfelt
Europe


"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
HERE
If cell3.Value = cell1.Value Then
cell1.Interior.ColorIndex = 7
cell3.Interior.ColorIndex = 3
HERE
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.

Mike[_95_]
Macro for cross referencing
 
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.

Mark
Macro for cross referencing
 
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.

Jean-Yves[_4_]
Macro for cross referencing
 
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

--
Regards

Jean-Yves Tfelt
Europe


"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.

Mark
Macro for cross referencing
 
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.

Mark
Macro for cross referencing
 
Ok, So I've got the code working. But it keeps crashing! Any suggestions?

"Mark" wrote:

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.

ryguy7272
Macro for cross referencing
 
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...

--
RyGuy


"Mark" wrote:

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

"Mark" wrote:

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.

Mark
Macro for cross referencing
 
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.

"ryguy7272" wrote:

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...

--
RyGuy


"Mark" wrote:

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

"Mark" wrote:

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.


All times are GMT +1. The time now is 12:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com