That seemed to fix it. Thanks so much Dave. One more questions if you please,
what modification would it take to have it work with alpha-numeric characters
(i.e. MAC_5053)?

Tony

"Dave Peterson" wrote:

Try changing that line to this:

Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(1))





Tony S. wrote:

I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the
"here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a
lot" . I highlight B9 to E24 and run the macro. Sometimes it works and
sometimes I get:

Run-time error '13'"
Type mismatch

I hit "debug" and the following is highlighter in yellow:

Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

Hope this helps. Tony

"Dave Peterson" wrote:

Which line caused the error?

If you changed anything, post your new version.

Tony S. wrote:

Thanks for the code Dave. However, for so reason I get a "Run-time errer
'13': Type mismatch. It works sometimes with the same text in a single column
and other times it fails. Any ideas? I'll also check out David McRitchie's
page.
Thanks! Tony

"Dave Peterson" wrote:

Saved from a previous post:

Option Explicit
Option Compare Text
Sub testme()

Application.ScreenUpdating = False

Dim myWords As Variant
Dim myRng As Range
Dim foundCell As Range
Dim iCtr As Long 'word counter
Dim cCtr As Long 'character counter
Dim FirstAddress As String
Dim AllFoundCells As Range
Dim myCell As Range

'add other words here
myWords = Array("widgets", "assemblies", "another", "word", "here")

Set myRng = Selection

On Error Resume Next
Set myRng = Intersect(myRng, _
myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please choose a range that contains text constants!"
Exit Sub
End If

For iCtr = LBound(myWords) To UBound(myWords)
FirstAddress = ""
Set foundCell = Nothing
With myRng
Set foundCell = .Find(what:=myWords(iCtr), _
LookIn:=xlValues, lookat:=xlPart, _
after:=.Cells(.Cells.Count))

If foundCell Is Nothing Then
MsgBox myWords(iCtr) & " wasn't found!"
Else
Set AllFoundCells = foundCell
FirstAddress = foundCell.Address
Do
If AllFoundCells Is Nothing Then
Set AllFoundCells = foundCell
Else
Set AllFoundCells = Union(foundCell, AllFoundCells)
End If
Set foundCell = .FindNext(foundCell)

Loop While Not foundCell Is Nothing _
And foundCell.Address < FirstAddress
End If

End With

If AllFoundCells Is Nothing Then
'do nothing
Else
For Each myCell In AllFoundCells.Cells
For cCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _
= myWords(iCtr) Then
With myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr)))
.Font.ColorIndex = 3
.Font.Bold = True
End With
End If
Next cCtr
Next myCell
End If
Next iCtr
Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

This portion:

With myCell.Characters(Start:=cCtr, _
Length:=Len(myWords(iCtr)))
.Font.ColorIndex = 3
.Font.Bold = True
End With

Changes the color and the boldness.

You may want to save first--so you can close without saving if you don't want to
keep the formatting changes. Remember this kind of formatting only works on
text cells--not formulas--not numbers.

Tony S. wrote:

Is it possible to use an input box (or other means of entry) to find and
change the properties of just the inputted text only within any column
selected? I have macros that change the cell color or all the text in the
cell, but I want to just highlight or bold or re-color the word of the text
I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50
SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the
appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003.

I've used conditional formatting and been to Chip Pearson's site but can't
find the answer. Any help is greatly appreciated.
Tony

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson