How can I change the color of a single word inside a cell full of text in excel using VBA

Thanks Ossie,

Had a few personal problems but now I came back to work with this issue.

Thanks for the correction, it worked perfectly with minor adjustments! thanks a lot!

I did not realize that you wanted to search the entire worksheet for multiple occurrences of the word in different cells

Try the following code which I have now modified to find multiple occurrences of the word in any particular cellas well as multiple occurrences throughout the worksheet.

Private Sub MakeRED_Click()

    Dim ws As Worksheet

    Dim rngCelToFind As Range

    Dim strToFind As String

    Dim lngFirst As Long

    Dim lngLength As Long

    Dim lngStart As Long

    Dim i As Long

    Dim strFirstAddr As String

    Set ws = Worksheets("Sheet1")       'Edit "sheet1" to your sheet name

    strToFind = "within"        'Example will search for cell containing word enclosed in double quotes

    'Assign the length of the word to a variable

    lngLength = Len(strToFind)

    With ws.Cells

        'rngCellToFind will contain the full reference of the cell where the word is found

        Set rngCelToFind = .Find(What:=strToFind, _

                            LookIn:=xlFormulas, _

                            LookAt:=xlPart, _

                            SearchOrder:=xlByRows, _

                            SearchDirection:=xlNext, _

                            MatchCase:=False, _

                            SearchFormat:=False)

        If Not rngCelToFind Is Nothing Then

            strFirstAddr = rngCelToFind.Address    'Save address of first cell found

            Do

                'Loop through the entire string in the cell and

                'find and format all occurrences of the word

                lngStart = 1

                For i = 1 To Len(rngCelToFind.Value)

                    lngFirst = InStr(lngStart, rngCelToFind.Value, strToFind)

                    If lngFirst = 0 Then Exit For   'If no more occurrences in the same cell

                    With rngCelToFind.Characters(lngFirst, lngLength).Font

                        .Color = RGB(0, 0, 255) 'Red

                        .Bold = True

                    End With

                    lngStart = lngStart + lngLength

                Next i

                Set rngCelToFind = .FindNext(rngCelToFind)

                If rngCelToFind Is Nothing Then Exit Do 'If not found.

            'FindNext loops around to start of search after last find

            'Therefore test if looped around to the first address found and if so then exit

            Loop While rngCelToFind.Address <> strFirstAddr

        Else

            MsgBox "No cells found containing word " & strToFind

        End If

    End With

 End Sub

Try the following. Example not exactly as your code but you should be able to get the idea.

For the example I placed text in a cell and it contained the word "within". It first searches the worksheet for the word and then searches the text of the cell where the word is found to get the start position and length of the required word is also required.

Options to set the color are using the color constants, color number or the RGB color.

To get the RGB code of a color.

• On the worksheet select any cell with text
• Select the font color dropdown
• Select More colors
• On the standard tab (at top) select a color from the behive
• Select the Custom tab (at top) and you will see the RGB code
• You can initially select a color from the first palette but it then closes the dropdown. However select the font dropdown again and then More colors and go to the Custom tab.

Private Sub MakeRED_Click()

    Dim ws As Worksheet

    Dim rngCelToFind As Range

    Dim strToFind As String

    Dim lngFirst As Long

    Dim lngLength As Long

   

    Set ws = Worksheets("Sheet1")       'Edit "sheet1" to your sheet name

    strToFind = "within"        'Example will search for cell containing word "within"

   

    lngLength = Len(strToFind)   'Assign length of the word to a variable

    

    With ws.Cells

        'rngCellToFind will contain the full reference of the cell where the word is found

        Set rngCelToFind = .Find(What:=strToFind, _

                            LookIn:=xlFormulas, _

                            LookAt:=xlPart, _

                            SearchOrder:=xlByRows, _

                            SearchDirection:=xlNext, _

                            MatchCase:=False, _

                            SearchFormat:=False)

    End With

   

    'Find the start position of the word in the found range (cell)

    lngFirst = InStr(1, rngCelToFind.Value, strToFind)

   

'Set the font formats

    With rngCelToFind.Characters(lngFirst, lngLength).Font

        '.Color = vbRed      'Can use color number if required

        .Color = RGB(0, 0, 255)

        .Bold = True

    End With

 End Sub

First of all, thanks OssieMac!, I tried the code, but it only worked with the first cell with the selected word in it, the other cells were ignored, maybe I did something wrong

Try the following. Example not exactly as your code but you should be able to get the idea.

For the example I placed text in a cell and it contained the word "within". It first searches the worksheet for the word and then searches the text of the cell where the word is found to get the start position and length of the required word is also required.

Options to set the color are using the color constants, color number or the RGB color.

To get the RGB code of a color.

• On the worksheet select any cell with text
• Select the font color dropdown
• Select More colors
• On the standard tab (at top) select a color from the behive
• Select the Custom tab (at top) and you will see the RGB code
• You can initially select a color from the first palette but it then closes the dropdown. However select the font dropdown again and then More colors and go to the Custom tab.

Private Sub MakeRED_Click()

    Dim ws As Worksheet

    Dim rngCelToFind As Range

    Dim strToFind As String

    Dim lngFirst As Long

    Dim lngLength As Long

    Set ws = Worksheets("Sheet1")       'Edit "sheet1" to your sheet name

    strToFind = "within"        'Example will search for cell containing word "within"

    lngLength = Len(strToFind)   'Assign length of the word to a variable

    With ws.Cells

        'rngCellToFind will contain the full reference of the cell where the word is found

        Set rngCelToFind = .Find(What:=strToFind, _

                            LookIn:=xlFormulas, _

                            LookAt:=xlPart, _

                            SearchOrder:=xlByRows, _

                            SearchDirection:=xlNext, _

                            MatchCase:=False, _

                            SearchFormat:=False)

    End With

    'Find the start position of the word in the found range (cell)

    lngFirst = InStr(1, rngCelToFind.Value, strToFind)

'Set the font formats

    With rngCelToFind.Characters(lngFirst, lngLength).Font

        '.Color = vbRed      'Can use color number if required

        .Color = RGB(0, 0, 255)

        .Bold = True

    End With

 End Sub