I am hoping someone can get me over a hurdle. I have a workbook with a dozen worksheets and each worksheet contains an image and several textboxes. I need to be able to loop through each worksheet in the workbook to find specific text in a textbox and once found, change the background color to yellow. The text in the text box could be repeated across several worksheets and I need each matching textbox to be colored. I found the following code that only works on a worksheet and makes the text bold and red:
So I did some more looking and modified the above code and placed in a module:
When I step through it, I see it looping through each worksheet, but it does not change the formatting of the textboxes. What am I missing? Your help is appreciated. Thank you.
Code:
Sub FindInShape2()
On Error Resume Next
Dim shp As Shape
Dim sFind As String
Dim sTemp As String
Dim iPos As Integer
Dim Response
sFind = InputBox("Search for?")
If Trim(sFind) = "" Then
MsgBox "Nothing entered"
Exit Sub
End If
sFind = LCase(sFind)
For Each shp In ActiveSheet.Shapes
sTemp = LCase(shp.TextFrame.Characters.Text)
iPos = InStr(sTemp, sFind)
If iPos > 0 Then
With shp.TextFrame.Characters(Start:=iPos, _
Length:=Len(sFind)).Font
.ColorIndex = 3
.Bold = True
End With
End If
Next
MsgBox "Finished"
End Sub
So I did some more looking and modified the above code and placed in a module:
Code:
Sub FindInTB()
On Error Resume Next
Dim wks As Worksheet, tb As TextBox
Dim sFind As String
Dim sTemp As String
Dim iPos As Integer
Dim Response
sFind = InputBox("Search for?")
If Trim(sFind) = "" Then
MsgBox "Nothing entered"
Exit Sub
End If
sFind = LCase(sFind)
For Each wks In ActiveWorkbook.Worksheets
For Each tb In wks.TextBoxes
sTemp = LCase(tb.TextFrame.Characters.Text)
iPos = InStr(sTemp, sFind)
If iPos > 0 Then
With tb.TextFrame.Characters(Start:=iPos, _
Length:=Len(sFind)).Font
.ColorIndex = 3
.Bold = True
End With
End If
Next tb
Next wks
MsgBox "Finished"
End Sub