Hi,

Try this

Sub stantiall()
Dim myrange, myrange1 As Range
Dim OwnedFunds As Variant
Lastrow = Cells(Rows.Count, "Q").End(xlUp).Row
Set myrange = Range("Q1:Q" & Lastrow)
OwnedFunds = Array("PSPFX", "TRREX", "NTHEX", "BUFBX", "VASVX", _
"ACTIX", "DISVX", "FAIRX", "HIINX")
For Each c In myrange
For I = LBound(OwnedFunds) To UBound(OwnedFunds)
If c.Value = OwnedFunds(I) Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
Next
If Not myrange1 Is Nothing Then
myrange1.Select
End If
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End Sub

Hope this is third time lucky!!

Mike


"G.R." wrote:

Hello. 3rd try, hope this post goes through.
Using Excel X for Mac. Need a macro to search column Q for a list of stock
ticker symbols for funds owned. When one is found, highlight that row and
shade it light green. Thought I would make my first try at an array and
cobbled together the macro below. It doesn't crash or return error messages
but it also doesn't work. Stepping through it I noticed it dumps out of the
Do loop after nine cycles and there are 9 fund names in the list (array).
Could be a clue, but to what I don't know. Any help would be appreciated.

Sub NLFI_Owned_Funds()
'
' NLFI_Owned_Funds Macro
' highlight owned funds
'
Dim OwnedFunds As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet
'use the ActiveSheet
Set sh = ActiveSheet

'search tickers in column Q
Set myRng = sh.Range("Q:Q")

'define array of TICKERS FOR OWNED FUNDS
OwnedFunds = Array("PSPFX", "TRREX", "NTHEX", "BUFBX", "VASVX", _
"ACTIX", "DISVX", "FAIRX", "HIINX")

'search the values in MyRng
With myRng

For I = LBound(OwnedFunds) To UBound(OwnedFunds)
Do
Set FoundCell = myRng.Find(What:=OwnedFunds(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
'possibly this should be LookIn:=xlValues
If FoundCell Is Nothing Then
Exit Do
Else
' select the entire row and shade light green
FoundCell.EntireRow.Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
Loop
Next I

End With
End Sub