Highlighting same text within a spreadsheet

[Peter]

Hi there,

Can anybody tell me if it is possible or if there is a
function available that you can use to highlight all of
the same text string within a worksheet?

The text can be found in varying columns and rows but I
would like to highlight the cells in which the text string
is found.

Thanks,
Peter

 

[JMay]

Use Conditional Formatting - With A1 as the Active Cell Select the Range of
cells involved (sayA1:G10).
then Menu, Format, Conditional Formatting, FormulaIs, then in box enter:
=SEARCH("Thewordyouarelookingfor",A1)>0, Format desired Pattern, etc.
OK, OK.
Enter away CF should alert you as to the entry
HTH

 

[Ken Wright]

One way:-

Sub ColText()

Dim i As Long
Dim k As Integer
Dim num As Long
Dim ans As String

ans = InputBox("What string do you want to find")
i = Application.WorksheetFunction.CountIf(ActiveSheet.UsedRange, "*" & ans & "*")

Cells.Find(What:=ans, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, MatchCase:=False).Activate
k = Application.WorksheetFunction.Find(ans, ActiveCell)

For num = 1 To i

With ActiveCell.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Cells.FindNext(After:=ActiveCell).Activate
Next num

End Sub