VBA Offset/Range Wierdness

[Thr33of4]

I have some code that I was able to piece together with the help of some online sleuthing. When the Range is F:F and I color/bold the text moving to the left (towards column A), it works like a charm.

However, if I change the Range to A:A and attempt to color/bold the text moving to the right (towards column F), it only cold/bolds the initial user inputted string? Can someone help?

Code that works

Public Sub FirstRowRedText()
    Set MyRange = Range("F:F")  'The Range that contains the substring you want to change color
    substr = InputBox("Enter the word to make Red", , "note")
    txtColor = 3   'The ColorIndex which repsents the color you want to change


    
    For Each myString In MyRange
        lenstr = Len(myString)
        lensubstr = Len(substr)
        For i = 1 To lenstr
            tempString = Mid(myString, i, lensubstr)
            If tempString = substr Then
                
                myString.Characters(Start:=i, Length:=lensubstr).Font.ColorIndex = txtColor
                myString.Characters(Start:=i, Length:=lensubstr).Font.Bold = True
                myString.Offset(, -5).Resize(1, 5).Font.ColorIndex = txtColor
                myString.Offset(, -5).Resize(1, 5).Font.Bold = True
            End If
         Next i
    Next myString

Code that doesn't work

Public Sub FirstRowRedText()
    Set MyRange = Range("A:A")  'The Range that contains the substring you want to change color
    substr = InputBox("Enter the word to make Red", , "note")
    txtColor = 3   'The ColorIndex which repsents the color you want to change


    
    For Each myString In MyRange
        lenstr = Len(myString)
        lensubstr = Len(substr)
        For i = 1 To lenstr
            tempString = Mid(myString, i, lensubstr)
            If tempString = substr Then
                
                myString.Characters(Start:=i, Length:=lensubstr).Font.ColorIndex = txtColor
                myString.Characters(Start:=i, Length:=lensubstr).Font.Bold = True
                myString.Offset(, 5).Resize(1, 5).Font.ColorIndex = txtColor
                myString.Offset(, 5).Resize(1, 5).Font.Bold = True
            End If
         Next i
    Next myString

Can someone figure out this baffling mystery?

 

[DanteAmor]

How about:
I changed the range of cells, so it doesn't search the entire column.

Public Sub FirstRowRedText2()
  Set MyRange = Range("A1", Range("A" & Rows.Count).End(3))  'The Range that contains the substring you want to change color
  substr = InputBox("Enter the word to make Red", , "note")
  txtColor = 3   'The ColorIndex which repsents the color you want to change
  
  For Each myString In MyRange
      lenstr = Len(myString)
      lensubstr = Len(substr)
      For i = 1 To lenstr
          tempString = Mid(myString, i, lensubstr)
          If tempString = substr Then
              
              myString.Characters(Start:=i, Length:=lensubstr).Font.ColorIndex = txtColor
              myString.Characters(Start:=i, Length:=lensubstr).Font.Bold = True
              myString.Offset(, 1).Resize(1, 5).Font.ColorIndex = txtColor
              myString.Offset(, 1).Resize(1, 5).Font.Bold = True
          End If
       Next i
  Next myString
End Sub

 

[Peter_SSs]

"Enter the word to make Red"

Just checking a couple of things in relation to this & what you are trying to do with your code.

1. It asks for a word to make red but your code colours the text even if it is not a word. Is that okay?
   For example if I enter "red" it does this: "The dog was coloured brown"
   
   
2. The code is case sensitive. If I enter "red" it does nothing to this text: "Red eyes". Is that okay?