OOps
a4 should be a5
[a2].Formula = "=sum(iferror(find(" & myList & ",a5),0))>0"
How about
Sub test()
Dim myList
myList = Array("Health Canada Consmr Prod", "NRCan OEE", "CFIA", "data incomplete", "not complete")
myList = "{""" & Join(myList, """,""") & """}"
Columns(1).Insert
With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
.Cells(1).FormulaArray = "=if(sum(iferror(find(" & myList & ",b1),0)),1,""x"")"
.FillDown
.Value = .Value
.SpecialCells(2, 2).EntireRow.Hidden = True
End With
Columns(1).Delete
End Sub
Bookmarks