[RESOLVED] Compare range values

[Botillier]

I am still a nubi at VB and I am probably getting in over my head but, I think that you can help me figure this one out.

I have two ranges one is named “GL” the other “Stmt.” I need to compare each cell value of range “GL” to each cell value of range “Stmnt.” Below is a sorry example of what I am trying to do. As you can see range “GL” can vary in length and each cell can be compared to the cells in range “Stmt,” but at the moment I have to write out each cell in range “Stmt,” therefore in my code range “Stmnt” cannot very in length.” How can I compare each value in range “GL” to each value in range “Stmnt” when both vary in length? I do not have a problem selecting and naming the ranges when they vary only comparing the values.


Sub Macro1()
Range("a1:a15").Select
Selection.Name = "GL"
Range("b1:b10").Select
Selection.Name = "Stmt"

For Each cell In Range("GL")
If cell.Value = Range("b1") Then
cell.Interior.ColorIndex = 37
Range("b1").Interior.ColorIndex = 36
End If
If cell.Value = Range("b2") Then
cell.Interior.ColorIndex = 37
Range("b2").Interior.ColorIndex = 36
End If
If cell.Value = Range("b3") Then
cell.Interior.ColorIndex = 37
Range("b3").Interior.ColorIndex = 36
End If
If cell.Value = Range("b4") Then
cell.Interior.ColorIndex = 37
Range("b4").Interior.ColorIndex = 36
End If
If cell.Value = Range("b5") Then
cell.Interior.ColorIndex = 37
Range("b5").Interior.ColorIndex = 36
End If
If cell.Value = Range("b6") Then
cell.Interior.ColorIndex = 37
Range("b6").Interior.ColorIndex = 36
End If
If cell.Value = Range("b7") Then
cell.Interior.ColorIndex = 37
Range("b7").Interior.ColorIndex = 36
End If
If cell.Value = Range("b8") Then
cell.Interior.ColorIndex = 37
Range("b8").Interior.ColorIndex = 36
End If
If cell.Value = Range("b9") Then
cell.Interior.ColorIndex = 37
Range("b9").Interior.ColorIndex = 36
End If
If cell.Value = Range("b10") Then
cell.Interior.ColorIndex = 37
Range("b10").Interior.ColorIndex = 36
End If
Next cell
End Sub

[DKenny]

loop through each cell in the first range and compare it with each cell in the 2nd range....


Sub QuickAndDirty()
Dim Range1 As Range
Dim Range2 As Range
Dim Cell1 As Range
Dim Cell2 As Range

Set Range1 = Range("Rng1") 'Replace Rng1 with your 1st range name
Set Range2 = Range("Rng2") 'Replace Rng2 with your 2nd range name

For Each Cell1 In Range1.Cells
For Each Cell2 In Range2.Cells
If Cell1 = Cell2 Then
Cell1.Interior.ColorIndex = 36
Cell2.Interior.ColorIndex = 36
End If
Next Cell2
Next Cell1

End Sub

[Botillier]

Awesome!