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

 

[Jaafar Tribak]

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

What do you mean ? the code doesn' select any cells.

 

[abdelfattah]

i mean the code not work if i don't interfere from me by specify the filling cells what i want color them so, is there any way do that without specify the filling cells

 

[Jaafar Tribak]

i mean the code not work if i don't interfere from me by specify the filling cells what i want color them so, is there any way do that without specify the filling cells

I am sorry but I don't quite understand what you mean. Do you want to apply the formatting to a predifined range ?

 

[abdelfattah]

i have succeeded my adjusting and works perfectly i adjust this

Set cellRange = Selection

to this

Set cellRange = ActiveSheet.UsedRange

i have last thing if you don't mind how can i return black color for old data colored by blue when i color a new data

 

[Jaafar Tribak]

If your initial data has black font throughout the entire used range then you could try this

Sub highlighttext()

    Dim cell As Range
    Dim cellRange As Range
    Dim textStart As Integer
    Dim wordToFind As String
     
    Application.ScreenUpdating = False
   
    Set cellRange = ActiveSheet.UsedRange
   
    cellRange.font.Color = 0
    cellRange.font.Bold = 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

 

[abdelfattah]

well done jaffar thanks for every thing , now your code is complete

 

[Jaafar Tribak]

well done jaffar thanks for every thing , now your code is complete

Glad you got this working in the end and thanks for the feedback ... Good luck with your project!