VBA 单元格字体设置

Option Explicit

Sub Font_1()
    Dim r As Long, c As Long, i As Integer, num As Integer, str As String
    Dim myRange As Range
    Dim myFon As Font
    Set myRange = ActiveSheet.UsedRange
    myRange.ClearFormats
    r = myRange.Rows.Count
    c = myRange.Columns.Count
    
    '查找语文成绩所在列号
    For i = 1 To c
        If myRange.Cells(1, i) = "语文" Then
            num = i
            With myRange.Cells(1, i)
                .RowHeight = Application.CentimetersToPoints(2)
                .ColumnWidth = Application.CentimetersToPoints(1)
                .VerticalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
                .HorizontalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
            End With
            MsgBox "语文成绩所在列号为： " & num
        End If
    Next
    
    '对语文成绩所在列进行条件筛选，然后改变其单元格文字格式
    For i = 2 To r
        If myRange.Cells(i, num) < 90 And myRange.Cells(i, num) _
            <> "" Then
            Debug.Print myRange.Cells(i, num)
            Set myFon = myRange.Cells(i, num).Font
            With myFon
                .Name = "楷体"
                .Size = 15
                .Bold = True
                .Italic = True
                .Color = RGB(255, 0, 0)
                .Strikethrough = True '水平删除线
                .Underline = xlUnderlineStyleNone 'xlUnderlineStyleSingle 'xlUnderlineStyleDouble
                '.Shadow = False  是/否无变化？？
                .Subscript = False
                .Superscript = False
                '具体属性设置参看：https://docs.microsoft.com/zh-cn/office/vba/api/excel.xlpattern
            End With
            
            With myRange.Cells(i, num)   '设置居中对齐
                .VerticalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
                .HorizontalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
            End With
            
            myRange.Cells(i, num).Select
        End If
    Next
    
    '查找英语成绩所在列号
    For i = 1 To c
        If myRange.Cells(1, i) = "英语" Then
            num = i
            With myRange.Cells(1, i)
                .RowHeight = Application.CentimetersToPoints(2)
                .ColumnWidth = Application.CentimetersToPoints(1)
                .VerticalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
                .HorizontalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
            End With
            MsgBox "英语成绩所在列号为： " & num
        End If
    Next
    
    '对英文成绩所在列的单元格数据格式进行设置
    For i = 2 To r
        If myRange.Cells(i, num) <> "" Then
            Debug.Print myRange.Cells(i, num)
            With myRange.Cells(i, num)   '设置居中对齐
                .VerticalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
                .HorizontalAlignment = xlCenter  'xlRight  xlLeft  xlDistributed  xlGeneral
                .NumberFormat = "###.00"
            End With
            myRange.Cells(i, num).Select
        End If
    Next
End Sub