Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding a certain word throughout worksheet?
How do I do it?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding a certain word throughout worksheet?
Suestew,
Use the conditional formatting which can be found under Format Menu. Regards, Brotha lee "suestew" wrote: How do I do it? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding a certain word throughout worksheet?
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" wrote in message ... How do I do it? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding a certain word throughout worksheet?
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 wrote: How do I do it? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding a certain word throughout worksheet?
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 ... 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" wrote in message ... How do I do it? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding a certain word throughout worksheet?
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 wrote: How do I do it? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding a certain word throughout worksheet?
Thanks
Add this line to top of module. Option Compare Text Gord On Mon, 15 Dec 2008 15:40:09 -0800, JLGWhiz 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 wrote: How do I do it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for bolding and italicizing word strings | Excel Programming | |||
Bolding Subtotals | Excel Programming | |||
Bolding using VBA | Excel Programming | |||
Bolding Row from VB6 | Excel Programming | |||
Bolding | Excel Programming |