+ Reply to Thread
Results 1 to 11 of 11

Add 1 to font size of entire worksheet

Hybrid View

  1. #1
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Add 1 to font size of entire worksheet

    Is there a way to write a macro that will go throught a worksheet and add 1 to each cell's font size used in that worksheet? I have different fonts and sizes on the worksheet so i cant just select all and change it globally.

    my guess is it will looksmilar to this but i am not sure how to do it:

    For Each Worksheet In ActiveWorkbook.Worksheets
    With Cells.Font
    .Size = (current font size) +1
    End With
    Next
    Last edited by PY_; 02-28-2011 at 09:06 AM.

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Add 1 to font size of entire worksheet

    I would suggest to set defined size and font type you need to make it all identical, example

    Sub test1()
    Application.ScreenUpdating = False
    With ActiveSheet
        .UsedRange.Font.Size = 20
        .Name = "Arial Cyr"
    End With
    Application.ScreenUpdating = True
    End Sub

  3. #3
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Add 1 to font size of entire worksheet

    Thanks for the response.

    I believe i have it figured out.

    Sub Resize_Fonts()
        With Cells
            .Font.Size = Selection.Font.Size + 1
            End With
    End Sub

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Add 1 to font size of entire worksheet

    You don't need Selection & I would limit the code to the UsedRange
    Option Explicit
    
    Sub Resize_Fonts()
        With UsedRange.Cells
            .Font.Size = .Font.Size + 1
        End With
    End Sub
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Add 1 to font size of entire worksheet

    Thank you Roy for the input.

  6. #6
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Add 1 to font size of entire worksheet

    Along the same question, any idea why this doesnt this work?


    Private Sub FontFix()
        With UsedRange.Cells
        
            ' All unlocked cells format to Shrink To Fit
            If Not Cells.Locked Then
                .ShrinkToFit = True
                End If
                
            ' All Non-Bold fonts or Non-Wingding fonts change to Arial Narrow.
            If Not .Font.Bold = True And Not Cells.Locked And Not .Font.Name = "Wingdings" Then
                .Font.Name = "Arial Narrow"
                End If
                
            ' All Wingding fonts change to bold and size 18.
            If .Font.Name = "Wingdings" Then
                .Font.Size = 18
                .Font.Bold = True
                .ShrinkToFit = False
                End If
                
        End With
    End Sub

    I know i have something typed incorrectly because it bypasses all the IF statements as soon as it gets to them (as if each one was false).

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Add 1 to font size of entire worksheet

    no object to apply, no Sheet indicated
    With UsedRange.Cells
    it should be for example:
    With Activesheet.UsedRange.Cells

  8. #8
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Add 1 to font size of entire worksheet

    This is driving me nuts that i can't get this little thing to run right. I attached my sample file.
    Attached Files Attached Files

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Add 1 to font size of entire worksheet

    Try this
    Private Sub FontFix()
        Dim rCl As Range
        With ActiveSheet
            .Unprotect
            For Each rCl In .UsedRange.SpecialCells(xlCellTypeConstants)
                With rCl
                    ' All unlocked cells format to Shrink To Fit
                    If Not .Locked Then .ShrinkToFit = True
                    ' All unlocked cells + Non-Bold fonts + Non-Wingding fonts change to Arial Narrow.
                    If Not .Font.Bold = True And Not .Locked And Not .Font.Name = "Wingdings" Then .Font.Name = "Arial Narrow"
                    ' All Wingding fonts change to bold and size 18.
                    If .Font.Name = "Wingdings" Then
                        .Font.Size = 18
                        .Font.Bold = True
                        .ShrinkToFit = False
                    End If
                End With
            Next rCl
            .Protect
        End With
    End Sub

  10. #10
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: Add 1 to font size of entire worksheet

    Thank you roy! I did change it by adding my original question to the code (i missed the +1 portion when trying to make the code do more)

    I think i understand what i was missing: I basically didnt set an object to apply it to still (like waterserv mentioned) correct?




    Private Sub FontFix1()
        Dim rCl As Range
        With ActiveSheet
            .Unprotect
            For Each rCl In .UsedRange.SpecialCells(xlCellTypeConstants)
                With rCl
                    ' All unlocked cells format to Shrink To Fit
                    If Not .Locked Then
                        .ShrinkToFit = True
                        .Font.Size = Selection.Font.Size + 1
                        End If
                    ' All unlocked cells + Non-Bold fonts + Non-Wingding fonts change to Arial Narrow.
                    If Not .Font.Bold = True And Not .Locked And Not .Font.Name = "Wingdings" Then .Font.Name = "Arial Narrow"
                    ' All Wingding fonts change to bold and size 18.
                    If .Font.Name = "Wingdings" Then
                        .Font.Size = 18
                        .Font.Bold = True
                        .ShrinkToFit = False
                    End If
                End With
            Next rCl
            .Protect
        End With
    End Sub

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Add 1 to font size of entire worksheet

    You were applying to the range, it seems to work best in individual cells

+ 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