Macro using InStr not returning expected results.

[Jefe187]

I have macro that is to reference a list of word on a worksheet ("List")and then check for the list in a another worksheet ("sheet 1"). If a word is present in the list then certain formatting will be done to columns A:E of the sheet 1 worksheet. The macro partially works, except it fills the entire columns A:E in my sheet 1 worksheet. I am not a vba expert, I tried to join all the cells and separate with a "|" then use the InStr function to return a value or a 0. Can someone please provide advice on why this is not working correctly. Here is the code:

Public Sub HighlightListedValues()

Dim strConcatList As String
Dim Cell As Range


'Creates a string concatenating list of strings, separated by |s
'e.g. "item1|item2|item3|item4|"
For Each Cell In Sheets("List").Range("A1:A10")
strConcatList = strConcatList & Cell.Value & "|"
Next Cell


'For each used cell in Column A of sheet1, check whether the value in that cell
'is contained within the concatenated string
For Each Cell In Intersect(Sheets("Sheet1").Range("A:A"), Sheets("Sheet1").UsedRange)
If InStr(strConcatList, Cell.Value) > 0 Then 'InStr returns 0 if the string isn't found
Cell.Columns("A:E").Interior.Color = RGB(0, 0, 128) 'Highlights the row in blue if value found
Cell.Columns("A:E").Font.Color = RGB(255, 255, 255) 'Highlights the font in white if value found
Cell.Columns("A:E").Font.Bold = True 'Bolds the font
End If
Next Cell
End Sub

 

[Jonmo1]

Try changing these references
Cell.Columns("A:E")

To
Intersect(Cell.EntireRow, Columns("A:E"))

 

[Jefe187]

Thank you for the reply. It is still formatting the entire selection, rather than just the row that matches the text in "List" worksheet. I wish I could insert a screen shot, but I have recreated a basic example below. I need the Air Systems and Application Systems formatted as well as the next five columns to the right.

Air Systems				1,775.00	1,756.00
	LINES GP-A	126	1	25.00	12.00
	MANIFOLD G	3811	1	100.00	200.00
	SHIELD GP-	123	1	150.00	144.00
	TURBO GP	39126	1	1,500.00	1,400.00
Application Systems				204.00	216.00
	BASE GP-V	296	1	50.00	50.00
	CONNECTION	1231	1	150.00	150.00
	COVER GP	140746	1	4.00	16.00

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>