Solved: Locate values from List

[Emoncada]

I need a little help with searching for Values threw multiple Spreadsheets.
I have a Spreadsheet(Set1) with Values A3:A200. I need to find those values but they are spreadout threw 23 Spreadsheets.
Is there a vbscript or Conditional formatting that I can use to find those values and if found possibly change Cell Color, or font color, or even return Spreadsheet name and Cell it's located at?

Any help would be great.

Thanks

[Tinbendr]

Ctrl-H -> Options ->Within - Select WorkBook
Replace Tab -> Format -> Format - Choose color

[Emoncada]

Is there a way to look for them using a range? it's about 700 that need to be found

[Emoncada]

Ok I found the following that can possibly work but need to make a range into an array maybe someone can assist.

[VBA]Sub Color_cells_In_All_Sheets()
Dim FirstAddress As String
Dim MySearch As Variant
Dim myColor As Variant
Dim Rng As Range
Dim I As Long
Dim A As Long
Dim sh As Worksheet
Dim myarray As Variant

'Fill in the search Value and color Index
MySearch = Array("bob")
myColor = Array("3")
'You can also use more values in the Array
'MySearch = Array("ron", "jelle", "judith")
'myColor = Array("3", "6", "10")
For Each sh In ActiveWorkbook.Worksheets
'Fill in the Search range, for a range on each sheet
'you can use sh.Range("B1100")
With sh.Range("A2:O300")
'Change the fill color to "no fill" in all cells
.Interior.ColorIndex = xlColorIndexNone
For I = LBound(MySearch) To UBound(MySearch)
'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to MySearch(I)
Set Rng = .Find(What:=MySearch(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Interior.ColorIndex = myColor(I)
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next I
End With
Next sh
End Sub


[/VBA]

Any way I can change this line
[VBA]MySearch = Array("bob")[/VBA]
to something like Worksheets("Sheet1").Range ("A2:A313")
So it can look at those values?

[Kenneth Hobs]

Try:
[VBA]MySearch = WorksheetFunction.Transpose(Worksheets("Sheet1").Range ("A2:A313"))[/VBA]

[Tinbendr]

Any way I can change this line
[vba]MySearch = Array("bob")[/vba] to something like Worksheets("Sheet1").Range ("A2:A313")
So it can look at those values?

You were close. Wrap this for/next around the search

[vba]Dim aCell as Range
MySearch = Worksheets("Sheet1").Range ("A2:A313")

For Each aCell in MySearch
'search here
Next[/vba]
I'm not sure how your colors match with your search items. In your code comments, you have three search items and three colors, But I don't think you want 311 different colors. But if you do, take a look at this site.

[Tinbendr]

Try:
[vba]MySearch = WorksheetFunction.Transpose(Worksheets("Sheet1").Range ("A2:A313"))[/vba]

I've gotta remember that one.

[Emoncada]

[VBA]

MySearch = WorksheetFunction.Transpose(Worksheets("Sheet1").Range ("A2:A313"))

[/VBA]

Worked.

I changed [VBA]Rng.Interior.ColorIndex = myColor(I)[/VBA] to [VBA]Rng.Interior.ColorIndex = 3 'red[/VBA]

And it worked Just as I was looking for.

I appreciate the Help Kenneth & Tinbendr