Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace with formated data
Using MS Excel 2003 can I use Find to find data in a cell and replace it with
the same data but formated in red? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Replace with formated data
Maybe...
Does the cell contain other characters, too? If you wanted to change the cells that contain Jocelyn (exactly, nothing more), then you can use Edit|replace. There's an option to change the formatting, too. If your cells contain other stuff: "Tell Jocelyn to change the font", you'd have to use a macro that loops through the cell and formats the text the way you want. 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(1)) 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 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. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Jocelyn wrote: Using MS Excel 2003 can I use Find to find data in a cell and replace it with the same data but formated in red? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace data | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
How to find/replace data in column | Excel Discussion (Misc queries) | |||
How to find/replace data in column | New Users to Excel | |||
Data Validation / find & replace | Excel Discussion (Misc queries) |