Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Highlighting Found Text in Excel
How can one find key words in an Excel document and programmatically
highlight the found key words? Best regards |
#2
|
|||
|
|||
In xl2002+, you can use Edit|Replace and specify a format for the whole cell
after the change. (Change the string to itself and specify the formatting you like under the Options button. But that affects the whole cell--may not be what you want if there are other words in those cells.) Saved from a previous post (or two!): If you want to change the color of just the characters, you need VBA in all versions. You want a macro???? 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") 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 myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) _ .Font.colorindex = 3 End If Next cCtr Next myCell End If Next iCtr Application.ScreenUpdating = True End Sub This line: myCell.Characters(Start:=cCtr, _ Length:=Len(myWords(iCtr))) _ .Font.colorindex = 3 changes the color. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Wordgeek wrote: How can one find key words in an Excel document and programmatically highlight the found key words? Best regards -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting excel to text file | Excel Discussion (Misc queries) | |||
How do I convert excel file into ASCII text file with alignment? | Excel Discussion (Misc queries) | |||
Wrap text limits in Excel 2003 cell formatting | Excel Discussion (Misc queries) | |||
I want Excel to allow cells with formulas and unrelated text | Excel Discussion (Misc queries) | |||
How do I fix text wrapping in Excel? | Excel Discussion (Misc queries) |