troubleshooting Question

VBA Script to change font (size&style) for specific text in document

Avatar of Aaron Roessler
Aaron Roessler asked on
VBAMicrosoft ExcelPCMicrosoft Office
16 Comments3 Solutions85 ViewsLast Modified:
All my Catalog Excel files have 3 different font styles.
1. Title of Products - this is Blue text font size 11.5
2. under that (#1 Title of Products) is Bold Text and underlined font size 9
3. then is a list of part#'s font size 9 regular text

Currently the font is Helvetica for all of them but I need to change font. I can select the whole doc and change the font but I also need to update the font sizes for each of the above 1. 2, and 3 types of text in my doc. I am using Mac Excel and it does have the same Find Tool as on a PC so I need a VBA script to do this.

I Recorded a Macro but this requires me to manually select each type of row. I need the VBA script to be able to find each of the 1, 2, and 3 above and change font style.

Here is a sample of what the Macro recorded when changing the #1 Title of Products manually.

Sub fontchange()
'
' fontchange Macro
'

'
    Rows("8:8").Select
    With Selection.Font
        .Name = "MuktaMahee Bold"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "MuktaMahee Bold"
        .Size = 11.5
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub

Screen-Shot-2020-02-06-at-9.15.12-PM.jpgTomlinson.xls
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros