Finding Text and Changing Color Across Workbook VBA

Question

I have multiple worksheets and want to find a string and change the string's color in all worksheets. The following code works on the active worksheet but is not cycling through all, despite when "done" the MsgBox indicates so. Can someone review and help me correct the code.

Sub FindTextColorChange()
Dim iX As Integer
Dim FindString As String
Dim MyColor As Integer

FindString = InputBox("Enter Search Word or Phrase")
MyColor = InputBox("Enter Color Number for Font Color")
    
    For iX = 1 To ThisWorkbook.Worksheets.Count

        Set myRange = Range("A2:D10000")
        
        For Each myString In myRange
            lenstr = Len(myString)
            lenFindString = Len(FindString)
            For i = 1 To lenstr
                tempString = Mid(myString, i, lenFindString)
                If tempString = FindString Then
                    myString.Characters(Start:=i, Length:=lenFindString).Font.ColorIndex = MyColor
                End If
            Next i
        Next myString
    Next iX
    MsgBox "All Selected Words Changed"
End Sub

Answer 1

Try something like this:

Sub FindTextColorChange()
    Dim ws As Worksheet
    Dim FindString As String, c As Range, v
    Dim MyColor As Integer, pos As Long

    FindString = InputBox("Enter Search Word or Phrase")
    MyColor = InputBox("Enter Color Number for Font Color")
    
    For Each ws In ThisWorkbook.Worksheets         'easier way to loop over sheets
        For Each c In ws.Range("A2:D10000").Cells
            v = c.Value
            pos = InStr(1, v, FindString) 'faster than going character-by-character
            Do While pos > 0
                c.Characters(Start:=pos, Length:=Len(FindString)).Font.ColorIndex = MyColor
                pos = InStr(pos + Len(FindString), v, FindString) 'start looking after last found position
            Loop
        Next c
    Next ws
    MsgBox "All Selected Words Changed"
End Sub