Change Colour of Specific Words in cells

[christian2016]

Hi Guys,

What I need help with is a VBA macro to find the total range of say Column C.

Column C each cell will have a paragraph of text.

Then need to loop that range looking for specific words say (OFF, ON, UP, DOWN).
Probably best to temporarily set the cell text as UPPER case so no need to search for a combination of upper and lower case words.

If the text has been found then change only that specific word in the cell text to RED and BOLD.

Then go to the next cell in our range and repeat.

Any help is greatly appreciated.

Thanks

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ColorWords()
  Dim Position As Long, Cell As Range, W As Variant, Words As Variant, Txt As String
  Words = Array("TEXT", "WORD", "THEN")
  For Each Cell In Columns("C").SpecialCells(xlConstants)
    Txt = " " & UCase(Cell.Value) & " "
    For Each W In Words
      Position = InStr(Txt, W)
      Do While Position > 0
        If Mid(Txt, Position - 1, Len(W) + 2) Like "[!A-Z0-9]" & W & "[!A-Z0-9]" Then
          With Cell.Characters(Position - 1, Len(W)).Font
            .Bold = True
            .Color = vbRed
          End With
        End If
        Position = InStr(Position + 1, Txt, W)
      Loop
    Next
  Next
End Sub[/td]
[/tr]
[/table]

Note: This only highlights the word if it is a stand-alone non-embedded word.

 

[christian2016]

Thank you Rick works well.

Sorry i forgot to mention what happens if my range is say from column C to Column J. How Do I amend the macro to work across all these columns.

 

[Rick Rothstein]

Thank you Rick works well.

Sorry i forgot to mention what happens if my range is say from column C to Column J. How Do I amend the macro to work across all these columns.

Just change this line of code...

For Each Cell In Columns("C").SpecialCells(xlConstants)

to this...

For Each Cell In Columns("C:J").SpecialCells(xlConstants)

 

[christian2016]

nice and simple.

Thank you