Loop and Find
 

Hi

I have a list of names in sheet 1 column A, I would like to look up the
names, in all of sheet 2 (they are in multiple columns). If found I
would like the name in Sheet1 col A to be highlighted a different
colour, then to move to the next name in the list. If it is not found,
simply move to the next name in the list.

I have searched for hours online for this but am having no joy. Any
help would be greatly appreciated as I am going round in circles.

This was the code that got the closest.

Sub FindIt1()

Sheets("Sheet2").Select
Dim x As Range
Set x = Range("a2:a10")
With Worksheets(1).Range("a1:a10")
Set c = .Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
Sheets("Sheet2").Select
End If
End With
End Sub

Loop and Find
 

Try this Chad

With the list in Sheet1 and it look in Sheet2

Sub test()
Dim cell As Range
On Error Resume Next
For Each cell In Sheets("Sheet1").Range("A:A").SpecialCells(xlCellT ypeConstants)
If Application.WorksheetFunction.CountIf(Sheets("Shee t2").Range("A:IV"), cell.Value) 0 Then
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = xlNone
End If
Next cell
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chad" wrote in message oups.com...
Hi

I have a list of names in sheet 1 column A, I would like to look up the
names, in all of sheet 2 (they are in multiple columns). If found I
would like the name in Sheet1 col A to be highlighted a different
colour, then to move to the next name in the list. If it is not found,
simply move to the next name in the list.

I have searched for hours online for this but am having no joy. Any
help would be greatly appreciated as I am going round in circles.

This was the code that got the closest.

Sub FindIt1()

Sheets("Sheet2").Select
Dim x As Range
Set x = Range("a2:a10")
With Worksheets(1).Range("a1:a10")
Set c = .Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
Sheets("Sheet2").Select
End If
End With
End Sub

Loop and Find
 

Hi Chad,

I can propose to you to use the following code:

dim r as range
dim rowsInSheet1 as integer
dim row as integer

rowsInSheet1 = sheets("Sheet1").usedrange.rows.count
for each r in sheets("Sheet2").usedrange
if r.value<"" then
for row = 1 to rowsInSheet1
with sheets("Sheet1").cells(row, 1)
if .value=r.value then
.Interior.Pattern=xlPatternGray50
end if
next row
end if
next r

Please keep in mind that the macro checks all cells in sheet2 (the used
range) that are not empty. If you know exactly the range of cells that
contain the names in sheet2 then replace UsedRange with the known range

best regards,
Martin

"Chad" wrote:

Hi

I have a list of names in sheet 1 column A, I would like to look up the
names, in all of sheet 2 (they are in multiple columns). If found I
would like the name in Sheet1 col A to be highlighted a different
colour, then to move to the next name in the list. If it is not found,
simply move to the next name in the list.

I have searched for hours online for this but am having no joy. Any
help would be greatly appreciated as I am going round in circles.

This was the code that got the closest.

Sub FindIt1()

Sheets("Sheet2").Select
Dim x As Range
Set x = Range("a2:a10")
With Worksheets(1).Range("a1:a10")
Set c = .Find(x, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
Sheets("Sheet2").Select
End If
End With
End Sub

Loop and Find
 

This code worked a treat. Thanks very much for your kind assistance, I
really appreciate it.

Take care

Chad