Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and boldface a single word everywhere in a worksheet
I want to write a formula that will locate a word everywhere it appears in a
worksheet and boldface it. I know that I can use 'find' and then boldface to accomplish this but I have a extremely large worksheet and would like to speed up the process. My knowledge of Excel is not advanced enough to even know where to begin. Is there a way to do this? Artis |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and boldface a single word everywhere in a worksheet
On Sat, 25 Oct 2008 14:28:53 -0500, "Artis Tiedemann"
wrote: I want to write a formula that will locate a word everywhere it appears in a worksheet and boldface it. I know that I can use 'find' and then boldface to accomplish this but I have a extremely large worksheet and would like to speed up the process. My knowledge of Excel is not advanced enough to even know where to begin. Is there a way to do this? Artis You will only be able to do this if the word you want to have boldfaced is either the ONLY word in the cell, or, if it is part of a string, the string is entered directly, and not as the result of a formula. If the word is the result of a formula, and is also part of a multiword string, it can still be bolded, but the original formula will have to be replaced by the text string. If these restrictions are satisfactory, please post back with more info as to the characteristics of the cells containing this word. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and boldface a single word everywhere in a worksheet
Hi,
You can do this with VBA or manually, but not a spreadsheet function. And a formula can not be formatted by character only the entire thing. -- Thanks, Shane Devenshire "Artis Tiedemann" wrote: I want to write a formula that will locate a word everywhere it appears in a worksheet and boldface it. I know that I can use 'find' and then boldface to accomplish this but I have a extremely large worksheet and would like to speed up the process. My knowledge of Excel is not advanced enough to even know where to begin. Is there a way to do this? Artis |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and boldface a single word everywhere in a worksheet
The word to boldface is in a text string that is entered directly and not a
result of a formula. "Ron Rosenfeld" wrote in message ... On Sat, 25 Oct 2008 14:28:53 -0500, "Artis Tiedemann" wrote: I want to write a formula that will locate a word everywhere it appears in a worksheet and boldface it. I know that I can use 'find' and then boldface to accomplish this but I have a extremely large worksheet and would like to speed up the process. My knowledge of Excel is not advanced enough to even know where to begin. Is there a way to do this? Artis You will only be able to do this if the word you want to have boldfaced is either the ONLY word in the cell, or, if it is part of a string, the string is entered directly, and not as the result of a formula. If the word is the result of a formula, and is also part of a multiword string, it can still be bolded, but the original formula will have to be replaced by the text string. If these restrictions are satisfactory, please post back with more info as to the characteristics of the cells containing this word. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and boldface a single word everywhere in a worksheet
On Mon, 3 Nov 2008 08:36:19 -0600, "Artis Tiedemann"
wrote: The word to boldface is in a text string that is entered directly and not a result of a formula. "Ron Rosenfeld" wrote in message .. . On Sat, 25 Oct 2008 14:28:53 -0500, "Artis Tiedemann" wrote: I want to write a formula that will locate a word everywhere it appears in a worksheet and boldface it. OK, you won't be able to do it with a formula, as functions cannot alter the environment. You will need to do it with a VBA Macro. Here's one example. <alt-F11 opens the VB Editor. Ensure your project is highlighted in the Project Explorer window, then Insert/Module and paste the code below into the window that opens. To use this, <alt-F8 opens the macro dialog box. Select the macro and RUN. It will prompt you for the word to bold and color red. It will bold all the strings that appear in the range that match what you enter. If you want to exclude those strings that are part of other strings: e.g. bold TIME but not the TIME in TIMELY, a bit more programming will be required. ================================== Option Explicit Sub BoldWord() Dim sWordToBold As String, lWordLength As Long Dim rRangeToSearch As Range Dim c As Range Dim i As Long sWordToBold = InputBox("what word do you want to Bold?") lWordLength = Len(sWordToBold) Set rRangeToSearch = Range("A1:B10") For Each c In rRangeToSearch c.Font.Bold = False c.Font.Color = vbBlack i = 1 Do Until i = Len(c.Text) i = InStr(i, c.Text, sWordToBold, vbTextCompare) If i = 0 Then Exit Do With c.Characters(i, lWordLength).Font .Bold = True .Color = vbRed End With i = i + 1 Loop Next c End Sub ====================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I scale down an Excel worksheet to a single Word page? | Excel Discussion (Misc queries) | |||
merging single worksheet files into a single workbook | Excel Discussion (Misc queries) | |||
Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
Save a single worksheet in Excel as a single file. | New Users to Excel | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) |