Solved: Change font colour when string found

[koala]

Hi forum,

I have a simple spreadsheet that has combinations of text and numbers. I want to change the font to Red & Bold on a certain word whereever it is found.

The spreadsheet changes often, and the word may be by itself, at the start / end or any part of a description.

I have attached a spreadsheet with a sample of the worksheet, and also a sample of how I need the final result to be. My poor attempt at the code is also there.

Any help is greatly appreciated.

Regards
Koala

[Bob Phillips]

Sub ColourWord()
Const SearchWord As String = "HELP"
Dim cell As Range
Dim pos As Long

For Each cell In ActiveSheet.UsedRange

pos = InStr(cell.Value, SearchWord)
If pos > 0 Then

With cell.Characters(pos, Len(SearchWord)).Font

.ColorIndex = 3
.Bold = True
End With
End If
Next cell
End Sub

[koala]

Thank you XLD,

That works perfect.

Kind Regards
Koala

[koala]

XLD,

My appologies,

I thought your response worked fine, as I only ran it on sample data.

The actual data values that I wish to have coloured are linked from different sheets, and the code only works where the criteria word is by itself.

I have updated and attached my spreadsheet again, and you can see where it works fine on the "Original" sheet, but not on the the "Data" sheet.

Are you able to assist further.

Regards
Koala

[Bob Phillips]

I don't think that can be done. The cell does not actually include the word Help, it has a formula that returns that text, and the cell does not inherit the sub-formatting of the original cell.