Search for and Higlight text within a cell

[John Fevens]

I am looking for a way to search through a workbook of multiple
sheets, looking in one of the columns for a list of keywords.

If the keyword is located, I want to change the colour of the text
matching the keywords, or just change the background colour of the
entire cell.

There will be a series of 5 or 6 groups of keywords, each representing
a different colour.

 

[Frank Kabel]

Hi John
try something like the following

sub change_color()
Dim rng as range
Dim cell as range
Set rng = ActiveSheet.Usedrange
for each cell in rng
With cell.value
Select Case .Value
Case "keyword 1": .Interior.ColorIndex = 3
Case "keyword 2": .Interior.ColorIndex = 10
'etc.
End Select
End With
next
End Sub

 

[John Fevens]

That is simalar to what I need, but I need to look at text within the
cell, that seems to only work if the keyword is the only string in the
cell.

Unless perhaps I am not using it right!

 

[Frank Kabel]

O.k.
try

sub change_color()
Dim rng as range
Dim cell as range
Set rng = ActiveSheet.Usedrange
for each cell in rng
With cell.
if instr(.value, "keyword1") then
.Interior.ColorIndex = 3
elseif instr(.value, "keyword2") then
.Interior.ColorIndex = 10
'etc
end if
End With
next
End Sub