Change colour of text if found in a column

[Sdwd76]

if a range of words are found (read, work, find) in col N
Then change the colour of these words to RED

is this possible please

vb button

 

[Peter_SSs]

• If the words are in col N, will they be in a cell by themselves or might a cell contain "I read a book"?
• Do you want the cell to turn red or just the text?
• Where is the list of words to look for?

Could you give us 5 or 6 examples to show the variety of what might be in col N and explain exactly what you you want in relation to those examples?

 

[Sdwd76]

The cell will have various words or asset number so i would want to find the words mentioned above but also codes like DIM484884 Is an old name so if DIM* is found then also change cell red

Which ever is easier ideally if the word matches then change the cell colour?

Lets say we have a sheet called Words, in Col A we can have a list of words going down 10 rows

 

[Peter_SSs]

Assuming the sheet with values in col N is the active sheet, see if this does what you want.

Sub FindStrings()
  Dim RX As Object
  Dim rngN As Range, c As Range
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = Application.TextJoin("|", 1, Sheets("Words").Range("A2", Sheets("Words").Range("A" & Rows.Count).End(xlUp)))
  Set rngN = Range("N2", Range("N" & Rows.Count).End(xlUp))
  rngN.Interior.Color = xlNone
  For Each c In rngN
    If RX.Test(c.Value) Then c.Interior.Color = vbRed
  Next c
End Sub