Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cross referencing | Excel Worksheet Functions | |||
Cross referencing | Excel Worksheet Functions | |||
Cross Referencing | Excel Discussion (Misc queries) | |||
Cross referencing | Excel Worksheet Functions | |||
cross referencing | Excel Worksheet Functions |