Sub Reformat()
Dim SrchRng3 As Range
Dim c3 As Range, f As String
Dim allpart As Range
'partnumber to search is based on value of A1 in Sheet2
Set allpart = Sheet2.Range("A1")'-------------------------help me on this only one cell value can be search,dont know how to add A2 A3 and so on...
'search partnumber from A1 to A65536 in sheet1
Set SrchRng3 = Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp))
Set c3 = SrchRng3.Find(allpart, LookIn:=xlValues)
If Not c3 Is Nothing Then
f = c3.Address
Do
With Sheet1.Range("A" & c3.Row & ":IV" & c3.Row)
'set color and font color if part number is searched
.Font.ColorIndex = 0
.Interior.ColorIndex = 37
End With
Set c3 = SrchRng3.FindNext(c3)
Loop While c3.Address <> f
End If
End Sub
can you help me add array or some code to search all value in sheet1 based on values in sheet2.
Bookmarks