change font of specific character

[kylefoley76]

I use a lot of subscripts in excel and I need this process automated. I already have it automated but the problem is sometimes the subscripts appear in font Openproofbold which is great and beautiful but other times the subscripts appear in Noteworthybold font which is ugly and I have a very tough time getting the font I want. I have two strategies for resolving this. I can use this subrouting:



Dim SearchString As String
Dim i As Integer
Dim FindChar As String


FindChar = ChrW(&H2081)
SearchString = activecell.value
For i = 1 To Len(SearchString)
If Mid(SearchString, i, 1) = Findchar Then
activecell.Characters(i, 1).Font.FontStyle = "Openproofbold"
End If
Next i

That subroutine however is not working. The ChrW(&H2081) is the character for subscript 1

I also tried

I also tried:

Dim SearchString As String
Dim i As Integer
Dim FindChar As String




SearchString = activecell.value
For i = 1 To Len(SearchString)
If Mid(SearchString, i, 1) = "W" Then
activecell.Characters(i, 1).Font.FontStyle = "Cambria"
End If
Next i



Just to test it out and it did not work. However, even if I get that subrouting working I don't know if it will solve my problem. My problem is that I reguarlarly need to convert subscripts to a helper symbol because I then copy and past the text onto microsoft word. And it is easier to copy onto Word if the symbols are in helper mode. This is what I mean:

gg.Replace What:=ChrW(&H2081), Replacement:="::1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I use that code to convert 'hey1' into 'hey::1' I then paste that onto word and then I use a word macro to covert 'hey::1' into 'hey1'. Trust me this is the best way to do things.

however, if instead of using

gg.Replace What:=ChrW(&H2081), Replacement:="::1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I could convert ::1 into a subscript using something like

activecell.Characters(i, 1).Font.subscript = True

but I think I've done that before and it does not work, because I also need to use this text to put into a python program and python does not register

activecell.Characters(i, 1).Font.subscript = True

as a subscript.

All in all I think the ideal solution would be if instead of writing

gg.Replace What:=ChrW(&H2081), Replacement:="::1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

I could write something like

gg.Replace What:=ChrW(&H2081), Replacement:="::1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, Fontsylte:="OpenproofBold"

does that syntax exist?


Also, there appears to be a bug in this forum. Sometimes when I hit return or delete I cannot type any more in the box. This happened 4 times during the writing of the post and I had to post the thread then edit it.

 

[kylefoley76]

For example, I see here https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-replace-method-excel

that the replaceformat is available. Can I somehow specify the font in that replaceformat?

 

[kylefoley76]

or maybe they have something similar to word where you can write

With ActiveDocument.Content.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Font.subscript = True
.Format = True
.Text = "(?)"
.font = "openproofbold:
.Replacement.Text = "\2"
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With

 

[kylefoley76]

I found this sub at
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-replaceformat-property-excel

and it seems to be exactly what I want but I'm getting an error message. run time error 438 at line
With Application.FindFormat.Font

object does not support this property or method


Sub MakeBold()


' Establish search criteria.
With Application.FindFormat.Font
.Name = "Noteworthy"
.FontStyle = "Bold"
.Size = 12
End With


' Establish replacement criteria.
With Application.ReplaceFormat.Font
.Name = "OpenproofBold"
.FontStyle = "Regular"
.Size = 12
End With




End Sub

 

[kylefoley76]

I found this article

https://www.mrexcel.com/forum/excel-questions/370732-run-time-error.html

and it seems that since I'm using excel 2011 then
findformat does not work on excel 2011