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