Julie,
Ken is setting the start position outside of the loop, instead of inside of
it, which means that it will always be the value of the first cell.
Try this amendment
Sub ColText()
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim num As Long
Dim ans As String
Dim str As String
ans = InputBox("What string do you want to find")
i = Application.WorksheetFunction.CountIf(ActiveSheet. UsedRange, "*" &
ans & "*")
j = Len(ans)
Cells.Find(what:=ans, after:=ActiveCell, LookIn:=xlValues, lookat:= _
xlPart, MatchCase:=False).Activate
For num = 1 To i
k = Application.WorksheetFunction.Find(ans, ActiveCell)
With ActiveCell.Characters(Start:=k, Length:=j).Font
.ColorIndex = 3
.Bold = True
End With
Cells.FindNext(after:=ActiveCell).Activate
Next num
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"JulieD" wrote in message
...
Hi ken
i'm having trouble with your code
i have in column A starting at row 1
the cat
the cat sat on the mat
on the cat
and in C4
the cat sat
when i run your code and tell it to highlight "cat"
i get
A1 - cat red & bold
A2 - cat red & bold
A3 - he -- out of 'the' -- red & bold ??????
C4 - cat red & bold
why is A3 different & not working
any ideas???
Cheers
JulieD
"Ken Wright" wrote in message
...
Hi Julie, you can you know (Bold and colour them anyway - not change the
pattern
partially though). :-)
This will prompt you for the string and then highlight all instances on
the
activesheet.
Sub ColText()
Dim i As Long
Dim j As Integer
Dim k As Integer
Dim num As Long
Dim ans As String
Dim str As String
ans = InputBox("What string do you want to find")
i = Application.WorksheetFunction.CountIf(ActiveSheet. UsedRange, "*" &
ans
&
"*")
j = Len(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.Characters(Start:=k, Length:=j).Font
'THE COLORINDEX BIT WILL COLOUR IT RED AS WELL - DELETE OR
'COMMENT IT OUT IF YOU DON'T WANT IT
.ColorIndex = 3
.Bold = True
End With
Cells.FindNext(after:=ActiveCell).Activate
Next num
End Sub
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--
"JulieD" wrote in message
...
Hi
have you tried format / conditional formatting - its easier that
writing
macros - BTW (as far as i know) you can't bold or highlight parts of a
cell
in excel, if that's what you're trying to achieve.
Cheers
JulieD
"Need Help" wrote in message
...
I am trying to create a macro that searches a column for specified
text
and Bolds or Highlights all instances.
This is easy in Word but I couldn't find the feature in EXCEL.
Thanks.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.598 / Virus Database: 380 - Release Date: 28/02/2004