Loop through cells and change font

Question

I'm trying to loop through all cells in a row and change the font size using the following criteria:

• If the font size is less than 10, then change the font size to 10

This works if all cells in the worksheet are the same font size. It returns null if any of the cells in the sheet have a different font size. If I have a font size of 8 in A1 and a size of 20 in A2, there is no change.

Sub SetSheetFont(ws As Worksheet)
    Dim x As Integer
    Dim NumRows As Long
    Application.ScreenUpdating = False
    NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
    Range("A1").Select
    With ws
        ' If the font size is lower than 10, set to 10
        For x = 1 To NumRows
            If .Cells.Font.Size < 10 Then .Cells.Font.Size = 10
            ActiveCell.Offset(1, 0).Select
        Next
        Application.ScreenUpdating = True
    End With
End Sub

The end goal is to loop through all cells in the column until there is a certain number of empty cells, then start on the next column (in this case B1).

How might I at least accomplish this in one column? I'm pretty sure I can get it working if I start there.

Answer 1

You can loop through all the cells in the UsedRange

Sub SetSheetFont(ws As Worksheet)
    Dim myCell As Range
    Application.ScreenUpdating = False
    With ws
        For each myCell in ws.UsedRange
            ' If the font size is lower than 10, set to 10
            If myCell.Font.Size < 10 Then myCell.Font.Size = 10
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Side note: in general, you want to avoid using select in your code

Answer 2

As per my comment, I think this could be a good usecase for FindFormat and ReplaceFormat:

Dim x As Double

'Set ReplaceFormat just once
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Font.Size = 10

'Set FindFormat in a For loop
For x = 1 To 9.5 Step 0.5
    Application.FindFormat.Clear
    Application.FindFormat.Font.Size = x
    ws.Cells.Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=True
Next x

This prevents iteration over all ws.Cells. The loop is necessary because we cant set something like: Application.FindFormat.Font.Size < 10. And because Font.Size will auto-adjust (at least for me) to the nearest 0.5 (and 1 being the smallest size) we can step from 1 to 9.5 with a step of 0.5.

And as per your description, you might want to change it up to ws.UsedRange as per @cybernetic.nomad mentioned. So it would read: ws.UsedRange.Replace...