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.

[TABLE="width: 501"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Air Systems[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] 1,775.00[/TD]
[TD] 1,756.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LINES GP-A[/TD]
[TD] 126[/TD]
[TD]1[/TD]
[TD] 25.00[/TD]
[TD] 12.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]MANIFOLD G[/TD]
[TD] 3811[/TD]
[TD]1[/TD]
[TD] 100.00[/TD]
[TD] 200.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]SHIELD GP-[/TD]
[TD] 123[/TD]
[TD]1[/TD]
[TD] 150.00[/TD]
[TD] 144.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]TURBO GP [/TD]
[TD] 39126[/TD]
[TD]1[/TD]
[TD] 1,500.00[/TD]
[TD] 1,400.00[/TD]
[/TR]
[TR]
[TD]Application Systems[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] 204.00[/TD]
[TD] 216.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]BASE GP-V [/TD]
[TD] 296[/TD]
[TD]1[/TD]
[TD] 50.00[/TD]
[TD] 50.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]CONNECTION[/TD]
[TD] 1231[/TD]
[TD]1[/TD]
[TD] 150.00[/TD]
[TD] 150.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]COVER GP [/TD]
[TD] 140746[/TD]
[TD]1[/TD]
[TD] 4.00[/TD]
[TD] 16.00[/TD]
[/TR]
</tbody>[/TABLE]