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.

You can selectively format parts of a cell.

For example: type (in the formula bar) "My ", then select 'bold' from
the format menu (or toolbar), then continue typing "example". When you
press 'Enter' just the word "example" is in bold - not the whole cell.

As for how to achieve this in VBA code - I wish I knew! The only way I
can think of achieving this is to concatenate 2 (or more) cells with
different formatting. So far I haven't been able to get this to work.

Good Luck!!

On or about 29/02/2004 00:37, JulieD was seen in the vicinity and
allegedly stated:

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.

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

Hi Graeme

thanks - you answered my next question
glad to see there's another Aussie awake at this time of the day
BTW best 'disguise' your email address unless you like receiving spam.

Cheers
JulieD

"Graeme Whelan" wrote in message
...
You can selectively format parts of a cell.

For example: type (in the formula bar) "My ", then select 'bold' from
the format menu (or toolbar), then continue typing "example". When you
press 'Enter' just the word "example" is in bold - not the whole cell.

As for how to achieve this in VBA code - I wish I knew! The only way I
can think of achieving this is to concatenate 2 (or more) cells with
different formatting. So far I haven't been able to get this to work.

Good Luck!!

On or about 29/02/2004 00:37, JulieD was seen in the vicinity and
allegedly stated:

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.

Hi Ken

thanks for this - and now for my next question - can you use conditional
formatting to bold / change colour one word in a cell?
or do you just use the code you supplied on the worksheet_change event?

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

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

Hi Julie
with conditional format you can only change the whole cell. For parts
of a cell you have to use a event procedure (similar to the one Ken
provided)

--
Regards
Frank Kabel
Frankfurt, Germany

JulieD wrote:
Hi Ken

thanks for this - and now for my next question - can you use
conditional formatting to bold / change colour one word in a cell?
or do you just use the code you supplied on the worksheet_change
event?

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

Hi Frank

thought so, thanks for confirming it for me ... never know until you ask :)

Cheers
JulieD

"Frank Kabel" wrote in message
...
Hi Julie
with conditional format you can only change the whole cell. For parts
of a cell you have to use a event procedure (similar to the one Ken
provided)

--
Regards
Frank Kabel
Frankfurt, Germany

JulieD wrote:
Hi Ken

thanks for this - and now for my next question - can you use
conditional formatting to bold / change colour one word in a cell?
or do you just use the code you supplied on the worksheet_change
event?

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

I probably screwed something up ( Not having a good day in that respect :-< ) -
I'll take a look.

--
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 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






---
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

I wasn't deriving k correctly. Instead of doing it for each cell, I had only
done it once initially. Try this:-

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------


<snip


---
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

Hi Ken

works great

thanks

Cheers
JulieD

"Ken Wright" wrote in message
...
I wasn't deriving k correctly. Instead of doing it for each cell, I had
only
done it once initially. Try this:-

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--


<snip


---
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

Do note though, that both the routines you have been given will only do the
first instance of the search word in any given cell, so not entirely like it
works in Word.

--
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 Ken

works great

thanks

Cheers
JulieD

"Ken Wright" wrote in message
...
I wasn't deriving k correctly. Instead of doing it for each cell, I had
only
done it once initially. Try this:-

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--


<snip


---
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






---
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

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

LOl - I twigged it once Julie gave me a pointer. Serves me right for creating a
single string and then copying and pasting it to test it on. Should have varied
it. :-(

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Bob Phillips" wrote in message
...
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








---
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

Bob - Tiscali or Pipex address now??

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Bob Phillips" wrote in message
...
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








---
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

Related problem:
I have a roster grid on a worksheet, and would like to have -
1. the selected (active) cell appear bold [I can do this part]
2. ALL other instances of the same (name) string on the worksheet show
in bold at the same time [no midea how]
3. This is the really clever bit - getting the bold formatting to follow
along when a different cell is selected.

So if I click on a cell containing "John Doe" then both it and all other
cells containing "John Doe" will appear BOLD. Then if I click on a
different cell containing the name "Fred Blogs" then both it and all
other cells containing "John Doe" will appear BOLD, and all other names
on the worksheet (including "John Doe") will now appear in normal text.

I think it can be done with a .SelectionChange event of some sort, but
when I try to go through all the cells to check for the required name
and change the formatting accordingly, I keep writing infinite loops and
have to give the '3 fingered salute' (ctrl-alt-del) to get out of excel.

Any help appreciated,

-Graeme


On or about 29/02/2004 02:02, JulieD was seen in the vicinity and
allegedly stated:

Hi Ken

works great

thanks

Cheers
JulieD

"Ken Wright" wrote in message
...

I wasn't deriving k correctly. Instead of doing it for each cell, I had

only

done it once initially. Try this:-

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------

--

It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------

--


<snip


---
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

Thanks I added conditional formatting to my Macro for the
column and it works.
Thanks.
-----Original 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.


.

Hi

Glad it works - certainly created a good discussion & i learnt something too
:)

Cheers
JulieD

"Need Help" wrote in message
...
Thanks I added conditional formatting to my Macro for the
column and it works.
Thanks.
-----Original 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.


.