Is it possible - find all of a named range?

[Chris]

For discussion sake, let's have three tabs in our spreadsheet:

Tab1 - complete hockey schedule (home team column A, visiting team column B)
Tab2 - column A has complete list of eastern teams and is named "EasternTeams"
Tab3 - column A has complete list of western teams and is named "WesternTeams"

Is it possible to have a find routine identify every game where a home team
is from the West?

As long as I use an individual and specific city, I can get it to work fine
ex.

Cells.Find(What:="Seattle", After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Then a little loop allows me to go through and highlight all of the Seattle
games.

Is it possible to find all instances of all Western teams (named range)? I
haven't managed to get this to work.

Thank you.

 

[JLGWhiz]

You will have to use some sort of loop, no matter where your data is located.
The Find function can accomodate only one value at a time. The find next
method is essentially a loop that continues to look for the same value in
additional locations.

 

[Chris]

I was afraid of that. So that's what I've done and it all works fine. I now
highlight the indidual cells. The code looks like this:

findString = Sheets("West Teams").Cells(checkRow, dataColumn).Value

While findString > " "

Sheets("Hockey Schedule").Select
Range("B1").Activate

Set c = Cells.Find(What:=findString, After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)

If Not c Is Nothing Then
Cells.Find(What:=findString, After:=ActiveCell,
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
FirstRow = ActiveCell.Row
Do
Selection.Font.Bold = True
Selection.Font.Italic = True
Cells.FindNext(After:=ActiveCell).Activate
Loop While Not Cells.Cells Is Nothing And ActiveCell.Row <>
FirstRow
End If

checkRow = checkRow + 1
findString = Sheets("West Teams").Cells(checkRow, dataColumn).Value

Wend


But is there a way to highlight the entire row?

 

[Mike Fogleman]

Selection.EntireRow.Font.Bold = True

Mike F