How do I do it?

Suestew,

Use the conditional formatting which can be found under Format Menu.


Regards,

Brotha lee

"suestew" wrote:

> How do I do it?
>
>

Sub Test()

BoldWord ActiveSheet, "my-word"

End Sub


Sub BoldWord(ws As Worksheet, sWord As String)
Dim pos As Long
Dim firstAddress As String
Dim vBold
Dim rng As Range
Dim cel As Range

On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, 2)
On Error GoTo 0
If rng Is Nothing Then Exit Sub

With rng

Set cel = .Find(what:=sWord, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not cel Is Nothing Then
firstAddress = cel.Address
Do
pos = InStr(1, cel.Value, sWord)
While pos
vBold = cel.Characters(pos, Len(sWord)).Font.Bold
If vBold = vbNull Then vBold = False
If Not vBold Then
cel.Characters(pos, Len(sWord)).Font.Bold = True
End If

pos = InStr(pos + 1, cel.Value, sWord)
Wend

Set cel = .FindNext(cel)
Loop While Not cel Is Nothing And cel.Address <> firstAddress

End If
End With

End Sub

Regards,
Peter T

"suestew" <(E-Mail Removed)> wrote in message
news:A4F8266D-49D4-401D-9099-(E-Mail Removed)...
> How do I do it?
>
>

Is the word part of a string or by itself?

Do you want to do it Via VBA or just use Conditinal Formatting?

Since you posted in the programming group, here is a macro for VBA method no
matter where the word is located on the sheet.

Sub Bold_Word()
Dim rng As Range
Dim Cell As Range
Dim myword As String
Dim start_str As Integer
Dim Mylen As Integer
Dim N As Single
myword = InputBox("Enter the word ")
If myword = "" Then Exit Sub
Mylen = Len(myword)
Set rng = ActiveSheet.UsedRange
For Each Cell In rng
Cell.Font.Bold = False
start_str = InStr(Cell.Value, myword)
If start_str Then
Cell.Characters(start_str, Mylen).Font.Bold = True
End If
Next Cell
End Sub


Gord Dibben MS Excel MVP

On Mon, 15 Dec 2008 12:11:02 -0800, suestew
<(E-Mail Removed)> wrote:

>How do I do it?
>

Probably want case insensitive

Change
> pos = InStr(1, cel.Value, sWord)
to
pos = InStr(1, cel.Value, sWord, vbTextCompare)
and change
> pos = InStr(pos + 1, cel.Value, sWord)
to
pos = InStr(pos + 1, cel.Value, sWord, vbTextCompare)

As written the routine only looks for the word in string constant cells, not
formulas.

Peter T


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Sub Test()
>
> BoldWord ActiveSheet, "my-word"
>
> End Sub
>
>
> Sub BoldWord(ws As Worksheet, sWord As String)
> Dim pos As Long
> Dim firstAddress As String
> Dim vBold
> Dim rng As Range
> Dim cel As Range
>
> On Error Resume Next
> Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, 2)
> On Error GoTo 0
> If rng Is Nothing Then Exit Sub
>
> With rng
>
> Set cel = .Find(what:=sWord, LookIn:=xlValues, _
> LookAt:=xlPart, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False)
> If Not cel Is Nothing Then
> firstAddress = cel.Address
> Do
> pos = InStr(1, cel.Value, sWord)
> While pos
> vBold = cel.Characters(pos, Len(sWord)).Font.Bold
> If vBold = vbNull Then vBold = False
> If Not vBold Then
> cel.Characters(pos, Len(sWord)).Font.Bold = True
> End If
>
> pos = InStr(pos + 1, cel.Value, sWord)
> Wend
>
> Set cel = .FindNext(cel)
> Loop While Not cel Is Nothing And cel.Address <> firstAddress
>
> End If
> End With
>
> End Sub
>
> Regards,
> Peter T
>
> "suestew" <(E-Mail Removed)> wrote in message
> news:A4F8266D-49D4-401D-9099-(E-Mail Removed)...
>> How do I do it?
>>
>>
>
>

Hey Gord, I like that one. Think I'll save it for future use.

"Gord Dibben" wrote:

> Is the word part of a string or by itself?
>
> Do you want to do it Via VBA or just use Conditinal Formatting?
>
> Since you posted in the programming group, here is a macro for VBA method no
> matter where the word is located on the sheet.
>
> Sub Bold_Word()
> Dim rng As Range
> Dim Cell As Range
> Dim myword As String
> Dim start_str As Integer
> Dim Mylen As Integer
> Dim N As Single
> myword = InputBox("Enter the word ")
> If myword = "" Then Exit Sub
> Mylen = Len(myword)
> Set rng = ActiveSheet.UsedRange
> For Each Cell In rng
> Cell.Font.Bold = False
> start_str = InStr(Cell.Value, myword)
> If start_str Then
> Cell.Characters(start_str, Mylen).Font.Bold = True
> End If
> Next Cell
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Mon, 15 Dec 2008 12:11:02 -0800, suestew
> <(E-Mail Removed)> wrote:
>
> >How do I do it?
> >
>
>

Thanks

Add this line to top of module.

Option Compare Text


Gord

On Mon, 15 Dec 2008 15:40:09 -0800, JLGWhiz
<(E-Mail Removed)> wrote:

>Hey Gord, I like that one. Think I'll save it for future use.
>
>"Gord Dibben" wrote:
>
>> Is the word part of a string or by itself?
>>
>> Do you want to do it Via VBA or just use Conditinal Formatting?
>>
>> Since you posted in the programming group, here is a macro for VBA method no
>> matter where the word is located on the sheet.
>>
>> Sub Bold_Word()
>> Dim rng As Range
>> Dim Cell As Range
>> Dim myword As String
>> Dim start_str As Integer
>> Dim Mylen As Integer
>> Dim N As Single
>> myword = InputBox("Enter the word ")
>> If myword = "" Then Exit Sub
>> Mylen = Len(myword)
>> Set rng = ActiveSheet.UsedRange
>> For Each Cell In rng
>> Cell.Font.Bold = False
>> start_str = InStr(Cell.Value, myword)
>> If start_str Then
>> Cell.Characters(start_str, Mylen).Font.Bold = True
>> End If
>> Next Cell
>> End Sub
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Mon, 15 Dec 2008 12:11:02 -0800, suestew
>> <(E-Mail Removed)> wrote:
>>
>> >How do I do it?
>> >
>>
>>