PDA

View Full Version : Solved: Locate values from List



Emoncada
02-02-2011, 08:33 AM
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
02-02-2011, 10:58 AM
Ctrl-H -> Options ->Within - Select WorkBook
Replace Tab -> Format -> Format - Choose color

Emoncada
02-02-2011, 11:02 AM
Is there a way to look for them using a range? it's about 700 that need to be found

Emoncada
02-02-2011, 01:14 PM
Ok I found the following that can possibly work but need to make a range into an array maybe someone can assist.

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("B1:D100")
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




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

Kenneth Hobs
02-02-2011, 05:23 PM
Try:
MySearch = WorksheetFunction.Transpose(Worksheets("Sheet1").Range ("A2:A313"))

Tinbendr
02-02-2011, 05:57 PM
Any way I can change this line
MySearch = Array("bob") 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

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

For Each aCell in MySearch
'search here
Next
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. (http://www.mvps.org/dmcritchie/excel/colors.htm#colorindex)

Tinbendr
02-02-2011, 05:59 PM
Try:
MySearch = WorksheetFunction.Transpose(Worksheets("Sheet1").Range ("A2:A313"))
I've gotta remember that one.

Emoncada
02-03-2011, 06:38 AM
MySearch = WorksheetFunction.Transpose(Worksheets("Sheet1").Range ("A2:A313"))


Worked.

I changed Rng.Interior.ColorIndex = myColor(I) to Rng.Interior.ColorIndex = 3 'red

And it worked Just as I was looking for.

I appreciate the Help Kenneth & Tinbendr