+ Reply to Thread
Results 1 to 8 of 8

Macro to convert several font sizes to another font size - with an exception

  1. #1
    Registered User
    Join Date
    02-01-2006
    Posts
    27

    Macro to convert several font sizes to another font size - with an exception

    Hi - I know how to use Find/Replace to convert one specific font size to another font size, one font at a time. The situation I have is this: I have worksheets where MANY different fonts have been used and I need assistance with a macro that will find and convert all font sizes, EXCEPT where 11 pt is already being used, to 12 pt. 11 pt is good where it is being used and want it to be left alone.

    Find/Replace won't do an "EVERYTHING EXCEPT (11 pt)" command, that I can figure out, and hope a macro might get the job done so that I don't have to do several Find/Replace commands for every font size used (too time consuming)?

    Thank you!

  2. #2
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Macro to convert several font sizes to another font size - with an exception

    Make sure you make a copy of your workbook and test the following code on the copy. Also, you did not specify specific worksheets within the workbook, so this code will apply your preferred formatting to all worksheets.

    -as-

    Please Login or Register  to view this content.
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Macro to convert several font sizes to another font size - with an exception

    you may be able to do 2 replaces
    1. Find 11 point fonts and replace with a background fill that is not currently used
    2. Set all fonts to 12 point
    3 find and replace #1 background fill with 11 point font

    totally untested though!
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    02-01-2006
    Posts
    27

    Re: Macro to convert several font sizes to another font size - with an exception

    Many Thanks....can't wait to give it a try!

  5. #5
    Registered User
    Join Date
    02-01-2006
    Posts
    27

    Re: Macro to convert several font sizes to another font size - with an exception

    Many Thanks...will try it today!

  6. #6
    Registered User
    Join Date
    02-01-2006
    Posts
    27

    Re: Macro to convert several font sizes to another font size - with an exception

    Hi again AlvaroSiza - tried your macro and it worked wonderfully. Thanks again. I'm wondering how to make the macro run on only the active worksheet. I tried editing the macro with a few different activesheet. commands but nothing I've tried is working. Wonder if you would provide some direction. Many thanks!

  7. #7
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: Macro to convert several font sizes to another font size - with an exception

    Here you go...

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    02-01-2006
    Posts
    27

    Re: Macro to convert several font sizes to another font size - with an exception

    AHHH! I was so close yet so far! Many thanks to you....what a great help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1