Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Is it possible to use an input box (or other means of entry) to find and
change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Sub clrinput()
myText = InputBox("Enter something", "Text") With Sheets(1).Range("B2") .Value = myText .Characters(1, Len(myText)).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
JLGWhiz, thanks for your prompt reply. However when I type "The dog is large"
in cell B2 and run the macro, the entire sentenced is replaced by the word "dog" in red. I would need to retain all the contents of the cell and just bold and/or color the word "dog". Did I enter something wrong? Tony "JLGWhiz" wrote: Sub clrinput() myText = InputBox("Enter something", "Text") With Sheets(1).Range("B2") .Value = myText .Characters(1, Len(myText)).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
The macro colors whatever you enter into the input box. So if you type
something directly into a cell and then run the macro and enter something different into the input box, it will replace whatever is in the cell with what you have entered into the input box. Apparently, what you really are asking about is how to replace or add text to text already in a cell and identify the replaced or added text by font change. That gets a little more complex, since it would require the user to make a choice up front of which they were going to do, add or replace. But it could be done with the same principle as the method using characters. You would just have to write the code for either replace or concatenate. But I think it can be done. "Tony S." wrote: JLGWhiz, thanks for your prompt reply. However when I type "The dog is large" in cell B2 and run the macro, the entire sentenced is replaced by the word "dog" in red. I would need to retain all the contents of the cell and just bold and/or color the word "dog". Did I enter something wrong? Tony "JLGWhiz" wrote: Sub clrinput() myText = InputBox("Enter something", "Text") With Sheets(1).Range("B2") .Value = myText .Characters(1, Len(myText)).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
You're correct in the fact that I want to search existing text and change the
appearance of text that is typed in an input box. I do not want to replace the value of the text. I thought there was a straight forward method to accomplish this. Now it appears like it may be over my head. If you come across anything I could try, I'd sure appreciate it if you passed it along. Thanks for trying. Tony "JLGWhiz" wrote: The macro colors whatever you enter into the input box. So if you type something directly into a cell and then run the macro and enter something different into the input box, it will replace whatever is in the cell with what you have entered into the input box. Apparently, what you really are asking about is how to replace or add text to text already in a cell and identify the replaced or added text by font change. That gets a little more complex, since it would require the user to make a choice up front of which they were going to do, add or replace. But it could be done with the same principle as the method using characters. You would just have to write the code for either replace or concatenate. But I think it can be done. "Tony S." wrote: JLGWhiz, thanks for your prompt reply. However when I type "The dog is large" in cell B2 and run the macro, the entire sentenced is replaced by the word "dog" in red. I would need to retain all the contents of the cell and just bold and/or color the word "dog". Did I enter something wrong? Tony "JLGWhiz" wrote: Sub clrinput() myText = InputBox("Enter something", "Text") With Sheets(1).Range("B2") .Value = myText .Characters(1, Len(myText)).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Saved from a previous post:
Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Thanks for the code Dave. However, for so reason I get a "Run-time errer
'13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
This is a little more simple that the one Dave posted, but if you just want
to highlight certain words of a string in a cell, this will let you do it. I might point out that you can just as easily do this manually. But if you have a lot of areas in the sheet to look and the criteria to find those ranges is the same, then it could be useful to use in a loop. Sub clrInput() myInput = InputBox("Enter text to highlight", "Text") With Worksheets(1).Range("B2") .Characters(InStr(Range("B2"), myInput), 3).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: You're correct in the fact that I want to search existing text and change the appearance of text that is typed in an input box. I do not want to replace the value of the text. I thought there was a straight forward method to accomplish this. Now it appears like it may be over my head. If you come across anything I could try, I'd sure appreciate it if you passed it along. Thanks for trying. Tony "JLGWhiz" wrote: The macro colors whatever you enter into the input box. So if you type something directly into a cell and then run the macro and enter something different into the input box, it will replace whatever is in the cell with what you have entered into the input box. Apparently, what you really are asking about is how to replace or add text to text already in a cell and identify the replaced or added text by font change. That gets a little more complex, since it would require the user to make a choice up front of which they were going to do, add or replace. But it could be done with the same principle as the method using characters. You would just have to write the code for either replace or concatenate. But I think it can be done. "Tony S." wrote: JLGWhiz, thanks for your prompt reply. However when I type "The dog is large" in cell B2 and run the macro, the entire sentenced is replaced by the word "dog" in red. I would need to retain all the contents of the cell and just bold and/or color the word "dog". Did I enter something wrong? Tony "JLGWhiz" wrote: Sub clrinput() myText = InputBox("Enter something", "Text") With Sheets(1).Range("B2") .Value = myText .Characters(1, Len(myText)).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Sorry, I forgot to make it for variable length input. Try this.
Sub clrInput() myInput = InputBox("Enter text to highlight", "Text") With Worksheets(1).Range("B2") .Characters(InStr(Range("B2"), myInput), Len(myInput)).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: You're correct in the fact that I want to search existing text and change the appearance of text that is typed in an input box. I do not want to replace the value of the text. I thought there was a straight forward method to accomplish this. Now it appears like it may be over my head. If you come across anything I could try, I'd sure appreciate it if you passed it along. Thanks for trying. Tony "JLGWhiz" wrote: The macro colors whatever you enter into the input box. So if you type something directly into a cell and then run the macro and enter something different into the input box, it will replace whatever is in the cell with what you have entered into the input box. Apparently, what you really are asking about is how to replace or add text to text already in a cell and identify the replaced or added text by font change. That gets a little more complex, since it would require the user to make a choice up front of which they were going to do, add or replace. But it could be done with the same principle as the method using characters. You would just have to write the code for either replace or concatenate. But I think it can be done. "Tony S." wrote: JLGWhiz, thanks for your prompt reply. However when I type "The dog is large" in cell B2 and run the macro, the entire sentenced is replaced by the word "dog" in red. I would need to retain all the contents of the cell and just bold and/or color the word "dog". Did I enter something wrong? Tony "JLGWhiz" wrote: Sub clrinput() myText = InputBox("Enter something", "Text") With Sheets(1).Range("B2") .Value = myText .Characters(1, Len(myText)).Font.ColorIndex = 3 End With End Sub "Tony S." wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Which line caused the error?
If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the
"here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Try changing that line to this:
Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(1)) Tony S. wrote: I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the "here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
That seemed to fix it. Thanks so much Dave. One more questions if you please,
what modification would it take to have it work with alpha-numeric characters (i.e. MAC_5053)? Tony "Dave Peterson" wrote: Try changing that line to this: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(1)) Tony S. wrote: I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the "here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
They should work ok.
The problem is that formulas can't be formatted this way and cells that are numeric can't be formatted this way. (Text cells that are all digits are fine, though.) Tony S. wrote: That seemed to fix it. Thanks so much Dave. One more questions if you please, what modification would it take to have it work with alpha-numeric characters (i.e. MAC_5053)? Tony "Dave Peterson" wrote: Try changing that line to this: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(1)) Tony S. wrote: I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the "here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
They did, they do. Youre terrific. I can't thank you enough. I'm been
beating my head so long, it feels good to stop. Thanks again! Tony "Dave Peterson" wrote: They should work ok. The problem is that formulas can't be formatted this way and cells that are numeric can't be formatted this way. (Text cells that are all digits are fine, though.) Tony S. wrote: That seemed to fix it. Thanks so much Dave. One more questions if you please, what modification would it take to have it work with alpha-numeric characters (i.e. MAC_5053)? Tony "Dave Peterson" wrote: Try changing that line to this: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(1)) Tony S. wrote: I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the "here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
And thanks for finding that error.
In all my other testing, the range to fix was contiguous, so the error never reared its ugly head. Tony S. wrote: They did, they do. Youre terrific. I can't thank you enough. I'm been beating my head so long, it feels good to stop. Thanks again! Tony "Dave Peterson" wrote: They should work ok. The problem is that formulas can't be formatted this way and cells that are numeric can't be formatted this way. (Text cells that are all digits are fine, though.) Tony S. wrote: That seemed to fix it. Thanks so much Dave. One more questions if you please, what modification would it take to have it work with alpha-numeric characters (i.e. MAC_5053)? Tony "Dave Peterson" wrote: Try changing that line to this: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(1)) Tony S. wrote: I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the "here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Hi Dave,
The routine you sent me work fine for preset values, but can you tell me which line(s) I would need to modify to allow a user input box to vary the query as needed? Thanks! Tony "Dave Peterson" wrote: And thanks for finding that error. In all my other testing, the range to fix was contiguous, so the error never reared its ugly head. Tony S. wrote: They did, they do. You€„˘re terrific. I can't thank you enough. I'm been beating my head so long, it feels good to stop. Thanks again! Tony "Dave Peterson" wrote: They should work ok. The problem is that formulas can't be formatted this way and cells that are numeric can't be formatted this way. (Text cells that are all digits are fine, though.) Tony S. wrote: That seemed to fix it. Thanks so much Dave. One more questions if you please, what modification would it take to have it work with alpha-numeric characters (i.e. MAC_5053)? Tony "Dave Peterson" wrote: Try changing that line to this: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(1)) Tony S. wrote: I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the "here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and change text color or style only
Replace this line:
myWords = Array("widgets", "assemblies", "another", "word", "here") With: myWords = inputbox(Prompt:="Enter a single word/phrase") if trim(mywords) = "" then exit sub end if mywords = array(mywords) Tony S. wrote: Hi Dave, The routine you sent me work fine for preset values, but can you tell me which line(s) I would need to modify to allow a user input box to vary the query as needed? Thanks! Tony "Dave Peterson" wrote: And thanks for finding that error. In all my other testing, the range to fix was contiguous, so the error never reared its ugly head. Tony S. wrote: They did, they do. You€„˘re terrific. I can't thank you enough. I'm been beating my head so long, it feels good to stop. Thanks again! Tony "Dave Peterson" wrote: They should work ok. The problem is that formulas can't be formatted this way and cells that are numeric can't be formatted this way. (Text cells that are all digits are fine, though.) Tony S. wrote: That seemed to fix it. Thanks so much Dave. One more questions if you please, what modification would it take to have it work with alpha-numeric characters (i.e. MAC_5053)? Tony "Dave Peterson" wrote: Try changing that line to this: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(1)) Tony S. wrote: I didn't change any code. I'm using it as it. In cells B15 & C18 I typed the "here" & in cells C10, C16, C18, C19, C20 & C22 "I typed I like widgets a lot" . I highlight B9 to E24 and run the macro. Sometimes it works and sometimes I get: Run-time error '13'" Type mismatch I hit "debug" and the following is highlighter in yellow: Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) Hope this helps. Tony "Dave Peterson" wrote: Which line caused the error? If you changed anything, post your new version. Tony S. wrote: Thanks for the code Dave. However, for so reason I get a "Run-time errer '13': Type mismatch. It works sometimes with the same text in a single column and other times it fails. Any ideas? I'll also check out David McRitchie's page. Thanks! Tony "Dave Peterson" wrote: Saved from a previous post: Option Explicit Option Compare Text Sub testme() Application.ScreenUpdating = False Dim myWords As Variant Dim myRng As Range Dim foundCell As Range Dim iCtr As Long 'word counter Dim cCtr As Long 'character counter Dim FirstAddress As String Dim AllFoundCells As Range Dim myCell As Range 'add other words here myWords = Array("widgets", "assemblies", "another", "word", "here") Set myRng = Selection On Error Resume Next Set myRng = Intersect(myRng, _ myRng.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "Please choose a range that contains text constants!" Exit Sub End If For iCtr = LBound(myWords) To UBound(myWords) FirstAddress = "" Set foundCell = Nothing With myRng Set foundCell = .Find(what:=myWords(iCtr), _ LookIn:=xlValues, lookat:=xlPart, _ after:=.Cells(.Cells.Count)) If foundCell Is Nothing Then MsgBox myWords(iCtr) & " wasn't found!" Else Set AllFoundCells = foundCell FirstAddress = foundCell.Address Do If AllFoundCells Is Nothing Then Set AllFoundCells = foundCell Else Set AllFoundCells = Union(foundCell, AllFoundCells) End If Set foundCell = .FindNext(foundCell) Loop While Not foundCell Is Nothing _ And foundCell.Address < FirstAddress End If End With If AllFoundCells Is Nothing Then 'do nothing Else For Each myCell In AllFoundCells.Cells For cCtr = 1 To Len(myCell.Value) If Mid(myCell.Value, cCtr, Len(myWords(iCtr))) _ = myWords(iCtr) Then With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm This portion: With myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) .Font.ColorIndex = 3 .Font.Bold = True End With Changes the color and the boldness. You may want to save first--so you can close without saving if you don't want to keep the formatting changes. Remember this kind of formatting only works on text cells--not formulas--not numbers. Tony S. wrote: Is it possible to use an input box (or other means of entry) to find and change the properties of just the inputted text only within any column selected? I have macros that change the cell color or all the text in the cell, but I want to just highlight or bold or re-color the word of the text I'm searching for. (i.e. If a cell contains "INSTALL FASTENERS WITH MPX 5-50 SEALANT PER BAC 4025 METHOD 3." I want to have the option to change the appearance of just "MPX 5-50" or just "BAC 4025". I'm using Excel 2003. I've used conditional formatting and been to Chip Pearson's site but can't find the answer. Any help is greatly appreciated. Tony -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change font color on search | Excel Worksheet Functions | |||
Protected sheet question -- way to allow user to change text alignment and font style? | Excel Programming | |||
How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
Change of text or background color doesn't change on the screen. | Excel Discussion (Misc queries) | |||
Search for text, change font color | Excel Programming |