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
[VBA]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("B1100")
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[/VBA]
Reply With Quote
Originally Posted by xld
