How to find if an array contains a string [duplicate]
Using the code from my answer to a very similar question:
Sub DoSomething()Dim Mainfram(4) As StringDim cell As Excel.RangeMainfram(0) = "apple"Mainfram(1) = "pear"Mainfram(2) = "orange"Mainfram(3) = "fruit"For Each cell In Selection If IsInArray(cell.Value, MainFram) Then Row(cell.Row).Style = "Accent1" End IfNext cellEnd SubFunction IsInArray(stringToBeFound As String, arr As Variant) As Boolean IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)End Function
Another simple way using JOIN and INSTR
Sub Sample() Dim Mainfram(4) As String, strg As String Dim cel As Range Dim Delim As String Delim = "#" Mainfram(0) = "apple" Mainfram(1) = "pear" Mainfram(2) = "orange" Mainfram(3) = "fruit" strg = Join(Mainfram, Delim) strg = Delim & strg For Each cel In Selection If InStr(1, strg, Delim & cel.Value & Delim, vbTextCompare) Then _ Rows(cel.Row).Style = "Accent1" Next celEnd Sub
Use the Filter() method as shown here - https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function