Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Formats per Cell
I'm trying to search for specific words in a MsExcel spreadsheet and format
them (using either a macro or formula) with multiple font colors (e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be BOLD RED, and all the other text would be black. I'm basically trying to highlight individual words only, but not the entire text string or the entire cell. Is there a way to do this with either a formula or macro? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Formats per Cell
A function only returns a value, you will need a macro for this.
You can tie the macro to a change event so it behaves more like a function. -- http://www.ExcelHelp.us Call me at 1-888-MY-ETHER ext. 01781474 "Timo" wrote: I'm trying to search for specific words in a MsExcel spreadsheet and format them (using either a macro or formula) with multiple font colors (e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be BOLD RED, and all the other text would be black. I'm basically trying to highlight individual words only, but not the entire text string or the entire cell. Is there a way to do this with either a formula or macro? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Formats per Cell
Timo
Sub color_words() Dim Cell As Range, tempR As Range, mystring As String, _ myword As String, rng As Range mystring = InputBox("Enter the search string") If mystring = "" Then Exit Sub For Each Cell In ActiveSheet.UsedRange If Cell.Value = UCase(mystring) Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then End End If tempR.Select myword = InputBox("Enter the word to format ") If myword = "" Then Exit Sub Mylen = Len(myword) Set rng = Selection rng.Cells.Font.ColorIndex = 0 For Each Cell In rng start_str = InStr(Cell.Value, UCase(myword)) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End Sub Gord Dibben MS Excel MVP On Wed, 26 Nov 2008 12:06:01 -0800, Timo wrote: I'm trying to search for specific words in a MsExcel spreadsheet and format them (using either a macro or formula) with multiple font colors (e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be BOLD RED, and all the other text would be black. I'm basically trying to highlight individual words only, but not the entire text string or the entire cell. Is there a way to do this with either a formula or macro? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Formats per Cell
This is a good start. Thanks! However, the only problem I have with this
sub is that it prompts me for the text string. I'm searching through several hundred different text strings and trying to highlight specific words if they appear in a specific column (within an entire workbook, not just one worksheet). Any further advice? "Gord Dibben" wrote: Timo Sub color_words() Dim Cell As Range, tempR As Range, mystring As String, _ myword As String, rng As Range mystring = InputBox("Enter the search string") If mystring = "" Then Exit Sub For Each Cell In ActiveSheet.UsedRange If Cell.Value = UCase(mystring) Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then End End If tempR.Select myword = InputBox("Enter the word to format ") If myword = "" Then Exit Sub Mylen = Len(myword) Set rng = Selection rng.Cells.Font.ColorIndex = 0 For Each Cell In rng start_str = InStr(Cell.Value, UCase(myword)) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End Sub Gord Dibben MS Excel MVP On Wed, 26 Nov 2008 12:06:01 -0800, Timo wrote: I'm trying to search for specific words in a MsExcel spreadsheet and format them (using either a macro or formula) with multiple font colors (e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be BOLD RED, and all the other text would be black. I'm basically trying to highlight individual words only, but not the entire text string or the entire cell. Is there a way to do this with either a formula or macro? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Formats per Cell
So the text string doesn't matter?
Just the single word in any text string is what you're looking for? Sub Highlight_Word() Dim rng As Range Dim ws As Worksheet 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) For Each ws In ActiveWorkbook.Worksheets ws.Select With Selection Set rng = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)) For Each Cell In rng Cell.Font.ColorIndex = 0 start_str = InStr(Cell.Value, myword) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End With Next ws End Sub Gord On Tue, 2 Dec 2008 11:00:01 -0800, Timo wrote: This is a good start. Thanks! However, the only problem I have with this sub is that it prompts me for the text string. I'm searching through several hundred different text strings and trying to highlight specific words if they appear in a specific column (within an entire workbook, not just one worksheet). Any further advice? "Gord Dibben" wrote: Timo Sub color_words() Dim Cell As Range, tempR As Range, mystring As String, _ myword As String, rng As Range mystring = InputBox("Enter the search string") If mystring = "" Then Exit Sub For Each Cell In ActiveSheet.UsedRange If Cell.Value = UCase(mystring) Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then End End If tempR.Select myword = InputBox("Enter the word to format ") If myword = "" Then Exit Sub Mylen = Len(myword) Set rng = Selection rng.Cells.Font.ColorIndex = 0 For Each Cell In rng start_str = InStr(Cell.Value, UCase(myword)) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End Sub Gord Dibben MS Excel MVP On Wed, 26 Nov 2008 12:06:01 -0800, Timo wrote: I'm trying to search for specific words in a MsExcel spreadsheet and format them (using either a macro or formula) with multiple font colors (e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be BOLD RED, and all the other text would be black. I'm basically trying to highlight individual words only, but not the entire text string or the entire cell. Is there a way to do this with either a formula or macro? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Formats per Cell
Close enough. I was able to modify your suggested macro to fit my needs. I
actually took out the InputBox so the macro would automatically check for the words, and had to eliminate the code resetting the font color to 0, so I use it to search for and modify several different words. Thank you!!!! "Gord Dibben" wrote: So the text string doesn't matter? Just the single word in any text string is what you're looking for? Sub Highlight_Word() Dim rng As Range Dim ws As Worksheet 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) For Each ws In ActiveWorkbook.Worksheets ws.Select With Selection Set rng = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)) For Each Cell In rng Cell.Font.ColorIndex = 0 start_str = InStr(Cell.Value, myword) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End With Next ws End Sub Gord On Tue, 2 Dec 2008 11:00:01 -0800, Timo wrote: This is a good start. Thanks! However, the only problem I have with this sub is that it prompts me for the text string. I'm searching through several hundred different text strings and trying to highlight specific words if they appear in a specific column (within an entire workbook, not just one worksheet). Any further advice? "Gord Dibben" wrote: Timo Sub color_words() Dim Cell As Range, tempR As Range, mystring As String, _ myword As String, rng As Range mystring = InputBox("Enter the search string") If mystring = "" Then Exit Sub For Each Cell In ActiveSheet.UsedRange If Cell.Value = UCase(mystring) Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then End End If tempR.Select myword = InputBox("Enter the word to format ") If myword = "" Then Exit Sub Mylen = Len(myword) Set rng = Selection rng.Cells.Font.ColorIndex = 0 For Each Cell In rng start_str = InStr(Cell.Value, UCase(myword)) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End Sub Gord Dibben MS Excel MVP On Wed, 26 Nov 2008 12:06:01 -0800, Timo wrote: I'm trying to search for specific words in a MsExcel spreadsheet and format them (using either a macro or formula) with multiple font colors (e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be BOLD RED, and all the other text would be black. I'm basically trying to highlight individual words only, but not the entire text string or the entire cell. Is there a way to do this with either a formula or macro? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Formats per Cell
Thanks.
Curious though. How does Excel and the macro know which word(s) to look for if you removed the inputbox? Gord Dibben MS Excel MVP On Fri, 5 Dec 2008 17:50:10 -0800, Timo wrote: Close enough. I was able to modify your suggested macro to fit my needs. I actually took out the InputBox so the macro would automatically check for the words, and had to eliminate the code resetting the font color to 0, so I use it to search for and modify several different words. Thank you!!!! "Gord Dibben" wrote: So the text string doesn't matter? Just the single word in any text string is what you're looking for? Sub Highlight_Word() Dim rng As Range Dim ws As Worksheet 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) For Each ws In ActiveWorkbook.Worksheets ws.Select With Selection Set rng = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)) For Each Cell In rng Cell.Font.ColorIndex = 0 start_str = InStr(Cell.Value, myword) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End With Next ws End Sub Gord On Tue, 2 Dec 2008 11:00:01 -0800, Timo wrote: This is a good start. Thanks! However, the only problem I have with this sub is that it prompts me for the text string. I'm searching through several hundred different text strings and trying to highlight specific words if they appear in a specific column (within an entire workbook, not just one worksheet). Any further advice? "Gord Dibben" wrote: Timo Sub color_words() Dim Cell As Range, tempR As Range, mystring As String, _ myword As String, rng As Range mystring = InputBox("Enter the search string") If mystring = "" Then Exit Sub For Each Cell In ActiveSheet.UsedRange If Cell.Value = UCase(mystring) Then If tempR Is Nothing Then Set tempR = Cell Else Set tempR = Union(tempR, Cell) End If End If Next Cell If tempR Is Nothing Then End End If tempR.Select myword = InputBox("Enter the word to format ") If myword = "" Then Exit Sub Mylen = Len(myword) Set rng = Selection rng.Cells.Font.ColorIndex = 0 For Each Cell In rng start_str = InStr(Cell.Value, UCase(myword)) If start_str Then Cell.Characters(start_str, Mylen).Font.ColorIndex = 3 End If Next Cell End Sub Gord Dibben MS Excel MVP On Wed, 26 Nov 2008 12:06:01 -0800, Timo wrote: I'm trying to search for specific words in a MsExcel spreadsheet and format them (using either a macro or formula) with multiple font colors (e.g. search for "MY NAME IS TIM", and format it so the word "NAME" would be BOLD RED, and all the other text would be black. I'm basically trying to highlight individual words only, but not the entire text string or the entire cell. Is there a way to do this with either a formula or macro? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple formats in a single cell with multiple formulas | Excel Worksheet Functions | |||
Multiple formats in the same cell | Excel Worksheet Functions | |||
Multiple formats within a cell | Excel Discussion (Misc queries) | |||
Multiple formats for a single cell value. | Excel Worksheet Functions | |||
how do i use multiple conditional formats in one cell? | Excel Discussion (Misc queries) |