Change Font Characters to a Specific Font While Preserving Symbol Font -- Need to Work with Cells That Have Formulas As Well

[kagekon]

Hi, I am using this code to change the characters in a cell from one font to another. For example, to change any character that is not "DesiredFont" to that font. I am also excluding the font "Symbol" because I mix a lot of symbols in my cell text. This, however, doesn't work for cells that contain formulas where the result is just a number or a concatenated string. Is there any way to make this work for cells that contain formulas as well (and not mess up my cells that contain mixtures of DesiredFont and Symbol font)?

Thanks!

Sub FormatFonts()

Dim Cl As Range
Dim i As Long

Excel.Application.ScreenUpdating = False

For Each Cl In ThisWorkbook.ActiveSheet.UsedRange.SpecialCells(xlConstants)
For i = 1 To Len(Cl.Value)
If Cl.Characters(i, 1).Font.Name <> Range("DesiredFont").Value And Cl.Characters(i, 1).Font.Name <> "Symbol" Then
Debug.Print Cl.Address
Cl.Characters(i, 1).Font.Name = Range("DesiredFont").Value
End If
Next i
Next Cl

Excel.Application.ScreenUpdating = True

End Sub

 

[RoryA]

Is there any way to make this work for cells that contain formulas as well

No, that is not possible.