colored words after search by inputbox

[abdelfattah]

hi
i try color this word "in " into any cell by blue and bold after search inputbox
this is my code but not works well
output

Sub highlighttext()
    Dim cell As Range
    Dim cellRange As Range
    Dim textStart As Integer
    Dim wordToFind As String
    Set cellRange = Selection
   
    Application.ScreenUpdating = False
   
    ' Ask the user what word they want to highlight
    wordToFind = InputBox(Prompt:="What word would you like to highlight?")
   
    For Each cell In cellRange
        textStart = InStr(cell.Value, wordToFind)
       
        cell.Characters(textStart, Len(wordToFind)).Font.Color = RGB(250, 0, 0)
    Next cell
   
    Application.ScreenUpdating = True

End Sub

 

[mikerickson]

You say that it doesn't work well, but don't say what it does that you don't like.
The only thing that I notice is that it does not turn all the characters black before coloring the new string. If that is what you want (multiple colored strings in the same cell) great, otherwise, the code should change the color to black before starting the loop.

 

[abdelfattah]

i got this code but i would adjusting to become any word i search colored and bold by blue in any cell

 

[Jaafar Tribak]

i got this code but i would adjusting to become any word i search colored and bold by blue in any cell

Is this what you mean ?

    For Each cell In cellRange
      textStart = InStr(cell.Value, wordToFind)
       With cell.Characters(textStart, Len(wordToFind)).font
            .Color = RGB(0, 0, 255)
            .Bold = True
        End With
    Next cell

 

[Jaafar Tribak]

Ignore the previous code and use this one whch takes care of the situation when the user enters a space in the inputbox

Sub highlighttext()

    Dim cell As Range
    Dim cellRange As Range
    Dim textStart As Integer
    Dim wordToFind As String
    Set cellRange = Selection
  
    Application.ScreenUpdating = False
  
    ' Ask the user what word they want to highlight
    wordToFind = InputBox("What word would you like to highlight?", , Selection.Value)
    
    For Each cell In cellRange
        If Len(Replace(wordToFind, " ", "")) Then
            textStart = InStr(1, cell.text, wordToFind)
            With cell.Characters(textStart, Len(wordToFind)).font
                .Color = RGB(0, 0, 255)
                .Bold = True
            End With
        End If
    Next cell
  
    Application.ScreenUpdating = True

End Sub

 

[abdelfattah]

not exactly jaafar i would color every cell contains my word chose not only in active cell with consider if the word locate in between two words it should the color chose word not all words

 

[Jaafar Tribak]

not exactly jaafar i would color every cell contains my word chose not only in active cell with consider if the word locate in between two words it should the color chose word not all words

I this this is what you mean :

Sub highlighttext()

    Dim cell As Range
    Dim cellRange As Range
    Dim textStart As Integer
    Dim wordToFind As String
    Set cellRange = Selection
  
    Application.ScreenUpdating = False
  
    ' Ask the user what word they want to highlight
    wordToFind = InputBox("What word would you like to highlight?")
    
    For Each cell In cellRange
        If Len(Replace(wordToFind, " ", "")) Then
            textStart = InStr(1, cell.text, wordToFind)
            With cell.Characters(textStart, Len(wordToFind)).font
                .Color = RGB(0, 0, 255)
                .Bold = True
            End With
        End If
    Next cell
  
    Application.ScreenUpdating = True

End Sub

 

[abdelfattah]

hi, jaffar your code is something wrong you can see my images

and i wouldn't specify cells , i would color without specify from me

 

[Jaafar Tribak]

I see:

Try this :

Sub highlighttext()

    Dim cell As Range
    Dim cellRange As Range
    Dim textStart As Integer
    Dim wordToFind As String
    Set cellRange = Selection
  
    Application.ScreenUpdating = False
  
    ' Ask the user what word they want to highlight
    wordToFind = InputBox("What word would you like to highlight?")
    
    For Each cell In cellRange
        If Len(Replace(wordToFind, " ", "")) Then
            textStart = InStr(1, cell.text, wordToFind)
            If textStart Then
                With cell.Characters(textStart, Len(wordToFind)).font
                    .Color = RGB(0, 0, 255)
                    .Bold = True
                End With
            End If
        End If
    Next cell
  
    Application.ScreenUpdating = True

End Sub

Note that if the cell contains more than one instance of the search word it will only highlight the first one .

 

[abdelfattah]

great work jaafar now it work but i have ever said repeatedly about specify cell if is possible i don't interfere from me i'm talking about this

    Set cellRange = Selection

i would color directly without select cell range