I'm having an issue with the macro code below. Basically it's looking for a
value in one worksheet and then assigning that value to a variable called
"Item." Then it looks in another worksheet for the value. If it doesn't find
it it goes back to the first sheet and highlights it. The problem I'm running
into is that the same value can be in the first worksheet more than once.
It's not highlighting the first instance of the value but it is highlighting
every instance after it, which I don't want it to do. Any ideas?

With Sheets("Kits").Range("C1:C150000")
Set Rng = .Find(What:=Item, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

If Not Rng Is Nothing Then
ActiveCell.Offset(1, 0).Activate
Item = ActiveCell.Value
Else
With Selection.Interior
.Pattern = x1Solid
.PatternColorIndex = x1Automatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(1, 0).Activate
Item = ActiveCell.Value
End If
End With

Try this

Sub CompareVals()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim YourRangeA As Range, YourRangeB As Range
Dim FoundCell As Range, ALastRow As Byte
Dim i As Variant

Set ws1 = ActiveWorkbook.Worksheets(1)
Set ws2 = ActiveWorkbook.Worksheets(2)
ALastRow = ws1.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Set YourRangeA = ws1.Range("A1:A" & ALastRow)
Set YourRangeB = ws2.Range("A1:IV65536")


For Each i In YourRangeA
Set FoundCell = YourRangeB.Find(What:=i, LookIn:=xlValues)
With YourRangeB
.Find What:=i, LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True
If FoundCell Is Nothing Then
i.Interior.ColorIndex = 6
Else
Application.StatusBar = "Found Value " & FoundCell
End If
End With
Next
End Sub


"Dave L" wrote:

I'm having an issue with the macro code below. Basically it's looking for a
value in one worksheet and then assigning that value to a variable called
"Item." Then it looks in another worksheet for the value. If it doesn't find
it it goes back to the first sheet and highlights it. The problem I'm running
into is that the same value can be in the first worksheet more than once.
It's not highlighting the first instance of the value but it is highlighting
every instance after it, which I don't want it to do. Any ideas?

With Sheets("Kits").Range("C1:C150000")
Set Rng = .Find(What:=Item, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

If Not Rng Is Nothing Then
ActiveCell.Offset(1, 0).Activate
Item = ActiveCell.Value
Else
With Selection.Interior
.Pattern = x1Solid
.PatternColorIndex = x1Automatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(1, 0).Activate
Item = ActiveCell.Value
End If
End With

This works somewhat, but I'm still getting some of the same issues happening.
It seems that if the value exisits in both sheets multiple times then it
flags it as not found. Any ideas?

"Office_Novice" wrote:

my last post + A few minor revisions. This may require you to modify a few
things Sheet names and ranges ... but it should do what you want.

Sub CompareVals()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim YourRangeA As Range, YourRangeB As Range
Dim FoundCell As Range, ALastRow As Long
Dim i As Variant

Set ws1 = ActiveWorkbook.Worksheets(1)
Set ws2 = ActiveWorkbook.Worksheets(2)
ALastRow = ws1.Cells(Cells.Rows.Count, 1).End(xlDown).Row
Set YourRangeA = ws1.Range("A1:A" & ALastRow)
Set YourRangeB = ws2.Range("A:IV")

For Each i In YourRangeA
Set FoundCell = YourRangeB.Find(What:=i, LookIn:=xlValues)
With YourRangeB
.Find What:=i, LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True
If FoundCell Is Nothing Then
i.Interior.ColorIndex = 6
ElseIf Not FoundCell Is Nothing Then
Application.StatusBar = "Found Value " & FoundCell
End If
End With
Next i
End Sub



"Dave L" wrote:

I'm having an issue with the macro code below. Basically it's looking for a
value in one worksheet and then assigning that value to a variable called
"Item." Then it looks in another worksheet for the value. If it doesn't find
it it goes back to the first sheet and highlights it. The problem I'm running
into is that the same value can be in the first worksheet more than once.
It's not highlighting the first instance of the value but it is highlighting
every instance after it, which I don't want it to do. Any ideas?

With Sheets("Kits").Range("C1:C150000")
Set Rng = .Find(What:=Item, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

If Not Rng Is Nothing Then
ActiveCell.Offset(1, 0).Activate
Item = ActiveCell.Value
Else
With Selection.Interior
.Pattern = x1Solid
.PatternColorIndex = x1Automatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.Offset(1, 0).Activate
Item = ActiveCell.Value
End If
End With