find and replace issue in macro
 

Hello,

I have a macro in Excel 2007 where I do a copy from one spreadsheet file and
special paste to another sheet in another file. This works fine and it was
created by recording a macro and then doing some minor modifications. I
wanted to then do a find and replace where I make certain rows bold and a
different color, so I recorded another macro to see what it did so I could
insert the relevant code into my original macro. I did that but the results
when using the macro change the size of the text, making it smaller. If I
then go into format cells and the text tab everything looks fine. I then
click on the font size to another size and then immediately back to the
original size it was already on and click ok and it rights itself. I am
wondering though why this has to be done to begin with - i.e. why does the
font size change?

Here's 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
.FontStyle = "Bold"
.Subscript = False
.ThemeColor = 4
.TintAndShade = -0.249977111
End With
Selection.Replace What:="AMEX", Replacement:="AMEX", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

ActiveCell.Offset(0, 5).Select

Any help would be appreciated.
Thanks.

find and replace issue in macro
 

This is only a guess, but make sure that ShrinkToFit is set to false.
--
Gary''s Student - gsnu2007k


"ben" wrote:

Hello,

I have a macro in Excel 2007 where I do a copy from one spreadsheet file and
special paste to another sheet in another file. This works fine and it was
created by recording a macro and then doing some minor modifications. I
wanted to then do a find and replace where I make certain rows bold and a
different color, so I recorded another macro to see what it did so I could
insert the relevant code into my original macro. I did that but the results
when using the macro change the size of the text, making it smaller. If I
then go into format cells and the text tab everything looks fine. I then
click on the font size to another size and then immediately back to the
original size it was already on and click ok and it rights itself. I am
wondering though why this has to be done to begin with - i.e. why does the
font size change?

Here's 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
.FontStyle = "Bold"
.Subscript = False
.ThemeColor = 4
.TintAndShade = -0.249977111
End With
Selection.Replace What:="AMEX", Replacement:="AMEX", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

ActiveCell.Offset(0, 5).Select

Any help would be appreciated.
Thanks.

find and replace issue in macro
 

I checked after running the macro and the shrintofit box was unchecked.
When I change ReplaceFormat:=True to ReplaceFormat:=False, it doesn't happen
but then I also lose the formatting.


Gary''s Student wrote:
This is only a guess, but make sure that ShrinkToFit is set to false.