unwanted superscript change in macro

B

ben

Hello,

I have a macro that does a copy/paste followed by a find and replace to
change the color of some text and make it bold. At the end of running the
macro though I discovered that it also changes the formatting to
superscript. I got around this by turning off superscript after the find and
replace but I am wondering if there is a way to stop it becoming superscript
in the first place? Here is the macro:

Selection.Copy
Windows("finances_2008.xlsx").Activate
Sheets("Spending").Select

ActiveCell.Offset(0, -1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
With Application.ReplaceFormat.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249977111
.ThemeFont = xlThemeFontNone
End With

' At this step superscript is turned on
Selection.Replace What:="AMEX", Replacement:="AMEX", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

' Here is where I have to go out of my way to
' turn off superscript
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeFont = xlThemeFontNone
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top