PDA

View Full Version : How to go through a list on two different worksheets comparing values using VBA



Hova
06-07-2012, 11:17 AM
Thanks in advance for everyone's help

I am trying to match 2 columns in one worksheet with 2 (or potentially more) columns in another worksheet. I have attached my excel which hopefully gives a good description of what I have been trying to do for the past week. I think I am on the right track but I don't know how to properly reference between the two worksheets.

Thanks!

If you have any question I'll do my best to answer them

Hova

JP2112
06-07-2012, 11:56 AM
Please do not multi-post.

http://stackoverflow.com/questions/10935896/how-to-go-through-a-list-on-two-different-worksheets-comparing-values-using-vba

Hova
06-07-2012, 12:00 PM
Sorry, Just trying to get to an answer

CodeNinja
06-07-2012, 02:25 PM
Try this...

Sub roadFinder2()
Dim i As Integer
Dim sLocation1 As String
Dim sLocation2 As String
Dim i2 As Integer

For i = 2 To Sheet1.Range("A1").End(xlDown) + 1
sLocation1 = Sheet1.Cells(i, 2)
sLocation2 = Sheet1.Cells(i, 3)
For i2 = 2 To Sheet2.Range("B65536").End(xlUp).Row
If Sheet2.Cells(i2, 2) = sLocation1 Then
If Sheet2.Cells(i2, 3) = sLocation2 Then
'match found
Sheet1.Cells(i, 4) = Sheet2.Cells(i2, 1)
Sheet1.Cells(i, 1).Interior.ColorIndex = 6
End If
Else
If Sheet2.Cells(i2, 2) = sLocation2 Then
If Sheet2.Cells(i2, 3) = sLocation1 Then
'match found
Sheet1.Cells(i, 4) = Sheet2.Cells(i2, 1)
Sheet1.Cells(i, 1).Interior.ColorIndex = 6
End If
End If
End If
Next i2
If Sheet1.Cells(i, 1).Interior.ColorIndex <> 6 Then
Sheet1.Cells(i, 1).Interior.ColorIndex = 3
End If

Next i

End Sub