format text in multiple cells

[paulyankson]

Hi Mr Excel,
I have multiple cells in excel containing multiple bits of info, mainly usernames and passwords. How can I select all cells at once and look for a specific word in all cells and format it as bold, blue font and underlined? If I try this using CTRL+H and only apply the formatting to the one word .... it just goes ahead and formats the entire cell that way. That's not what I want.
Many thanks for any tips.
Best,
Pau

 

[MARK858]

A bit of a vague description but try the code below

Sub FindWords()
    Dim myRng As Range, myWord As String, NumChars As Long
    Dim StartChar As Long, RngChar As Long, EndWord As Long

    myWord = InputBox("Please enter the word(s) to format", "WordSearch")

    NumChars = Len(myWord)
    
    Application.ScreenUpdating = False

    For Each myRng In ActiveSheet.UsedRange
        RngChar = Len(myRng)
        StartChar = InStr(1, myRng, myWord)
        
        Do Until StartChar >= RngChar Or StartChar = 0
            EndWord = StartChar + NumChars
            
            If Mid(myRng, StartChar - 1, 1) = " " Or StartChar = 1 Then
                
                If Mid(myRng, EndWord, 1) = " " Or EndWord >= RngChar Then
                
                    With myRng.Characters(Start:=StartChar, Length:=NumChars).Font
                        .FontStyle = "Bold"
                        .Color = vbBlue
                        .Underline = xlUnderlineStyleSingle
                    End With
                
                End If
            
            End If
            
            StartChar = InStr(EndWord, myRng, myWord)
        Loop
    Next
    
    Application.ScreenUpdating = True
End Sub

 

[paulyankson]

A bit of a vague description but try the code below

Sub FindWords()
    Dim myRng As Range, myWord As String, NumChars As Long
    Dim StartChar As Long, RngChar As Long, EndWord As Long

    myWord = InputBox("Please enter the word(s) to format", "WordSearch")

    NumChars = Len(myWord)
   
    Application.ScreenUpdating = False

    For Each myRng In ActiveSheet.UsedRange
        RngChar = Len(myRng)
        StartChar = InStr(1, myRng, myWord)
       
        Do Until StartChar >= RngChar Or StartChar = 0
            EndWord = StartChar + NumChars
           
            If Mid(myRng, StartChar - 1, 1) = " " Or StartChar = 1 Then
               
                If Mid(myRng, EndWord, 1) = " " Or EndWord >= RngChar Then
               
                    With myRng.Characters(Start:=StartChar, Length:=NumChars).Font
                        .FontStyle = "Bold"
                        .Color = vbBlue
                        .Underline = xlUnderlineStyleSingle
                    End With
               
                End If
           
            End If
           
            StartChar = InStr(EndWord, myRng, myWord)
        Loop
    Next
   
    Application.ScreenUpdating = True
End Sub

Thank you, Mark858 - I appreciate you taking the time to reply to my query - much appreciated. I found a separate workaround. Namely, I just copied the excel cells wholesale into MS Word and performed the CTRL+H function there and then copied the data back to Excel. That retained the formatting for the single words I was trying to isolate, without applying thsame formatting to the entire cell. Best, Paul