change font of specific character

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
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.
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
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

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
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
 

Forum statistics

Threads
1,136,780
Messages
5,677,688
Members
419,712
Latest member
LearningCR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top