PDA

View Full Version : Solved: working with multiple open workbooks



Solerpwr07
07-01-2009, 04:22 AM
Hey everyone, first time poster.
I am writing a program that that searches all open workbooks and their worksheets for the many entries of a particular number located randomly within the sheets (and highlights them). The code thus far can successfully locate all entries in the sheets of a workbook, but will not locate entries in all open workbooks.

The idea is have the user put the entry into a textbox, and when a commandbutton is clicked vba locates all cells with the entry in all open workbooks and their sheets, and highlights them. Thank you in advance

Private Sub Highlight_Click()
Dim FirstAddress As String
Dim MySearch As Variant
Dim myColor As Variant
Dim Rng As Range
Dim I As Long
Dim sh As Worksheet
Dim wBook As Workbook



MySearch = Array(" " + TextBox1, TextBox1)
myColor = Array("6", "6")



For Each wBook In Application.Workbooks()

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.Cells

'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
'to change font color use
'Rng.Font.ColorIndex = myColor(I)
'to change cell fill color use
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
Next wBook

End Sub

Bob Phillips
07-01-2009, 04:24 AM
Why don't you give us a clue as to what is/is not happening?

Solerpwr07
07-01-2009, 04:32 AM
Why don't you give us a clue as to what is/is not happening?
Sure. what is happening (which is correct) is that this sub is highlighting all cells in a workbook and its sheets that match what is entered in a textbox

what it is not doing is broadening the search to all open workbooks. this is what is desired. i have been unsuccessful thus far

mdmackillop
07-01-2009, 04:34 AM
Try
For Each wBook In Workbooks
For Each sh In wBook.Worksheets

Solerpwr07
07-01-2009, 04:39 AM
That looks great. Works perfect...Thanks for your help!!

mdmackillop
07-01-2009, 04:43 AM
Glad to help. You can mark the thread Solved using the Thread Tools dropdown.

Welcome to VBAX