I have an excel sheet with hundreds of rows and columns. Each cell is filled with random character that are times new roman, size 10, regular.
I need to find a single character "·" that is randomly placed in some cells and not in others.
Once I find this character I need to change the format to symbol, size 9, and bold.
Problem I cannot use the "find all" and "replace all" feature because that changes the entire cell format. How do I write a code to find and replace/change the "·" symbol format? I am very amateur with VBA.
Here's what I have attempted, but it does not work.
Sub bnm() With Selection.Find .ClearFormatting .Text = "·" .Replacement.ClearFormatting .Replacement.Text = "·" .Name = "Symbol" .FontStyle = "bold" .Size = 9 .Execute Replace:=wdReplaceAll, Forward:=True, _ Wrap:=wdFindContinue End With End Sub
Please help! Thank you.
I'm sure this could be prettier, but it works:
Sub Macro1() Dim firstRange As Range Dim currentRange As Range Set firstRange = Cells.Find(What:="·", LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False) Set currentRange = firstRange While Not currentRange Is Nothing Dim val As String Dim lastIndex As Integer val = currentRange.Value lastIndex = InStr(lastIndex + 1, val, "·") While lastIndex <> 0 With currentRange.Characters(Start:=lastIndex, Length:=1).Font .Name = "Symbol" .Size = 9 End With lastIndex = InStr(lastIndex + 1, val, "·") Wend Set currentRange = Cells.FindNext(After:=currentRange) If firstRange = currentRange Then Set currentRange = Nothing End If Wend End Sub
A limitation is that this only works if the formatting doesn't work if the cell value is determined via a formula for example a cell containing
="test: " & "·" cannot have special formatting for the