Excel——如何使用VBA操作單元格的格式

單元格的填充Cell Fills (Color, Patterns, & Gradients)

Sub Modify_Cell_Fill()
    'Change Fill Color
        Range("A1").Interior.Color = RGB(141, 180, 227)
    'Add Pattern (See xlPattern constants for choices)
        Range("B1").Interior.Pattern = xlDown
        Range("B1").Interior.PatternColor = RGB(141, 180, 227)
    'Add Gradients
        With Range("C1").Interior
            .Pattern = xlPatternLinearGradient
            .Gradient.Degree = 180
            'Adjust Color Stops
                'Clear Default Color Stops
                    .Gradient.ColorStops.Clear
                'Add A Color Stop
                    With .Gradient.ColorStops.Add(0)
                        .Color = RGB(255, 255, 255)
                    End With
                'Add Another Color Stop
                    With .Gradient.ColorStops.Add(1)
                        .Color = RGB(141, 180, 227)
                    End With
        End With
End Sub

修改字型 Modify Font Format (Color, Bold, Underline, Italicize, Size, & Font Type)

Sub Modify_Cell_Font()
    'Change Font Color on RGB Scale
        Range("A1").Font.Color = RGB(3, 5, 6)    
    'Make Font Italicized
        Range("A1").Font.Italic = True        
    'Make Font Bold
        Range("A1").Font.Bold = True
    'Adjust Font Size
        Range("A1").Font.Size = 14        
    'Underline Text
        Range("A1").Font.Underline = True       
    'Change Font Type
        Range("A1").Font.FontStyle = "Arial"
End Sub

修改單元格的部分內容的字型

Sub BoldCellsLastWord()
Dim rng As Range
Dim str As String
Dim x As Integer
Dim LastWord AsString

Set rng = Range("A1")

str = rng.Value

'Determine the Last Word in String
  LastWord = Right(str, Len(str) - (InStrRev(str, " ") - 1))

'Get the Character Start Position of the Last Word
  x = InStr(str, LastWord)

'Target just the Last Word and Bold it
  With rng.Characters(Start:=x, Length:=Len(str)).Font
    .FontStyle = "Bold"
  End With

End Sub

自動調整行高和列寬

Sub AutoFit_Columns()

'PURPOSE: How To AutoFit Worksheet Columns (Multiple Scenarios)
Dim sht As Worksheet

'AutoFit One Column
    ThisWorkbook.Worksheets("Sheet1").Columns("O:O").EntireColumn.AutoFit
    
'AutoFit Multiple Columns
    ThisWorkbook.Worksheets("Sheet1").Range("I:I,L:L").EntireColumn.AutoFit 'Columns I & L
    ThisWorkbook.Worksheets("Sheet1").Range("I:L").EntireColumn.AutoFit 'Columns I to L
    
'AutoFit All Columns on Worksheet
    ThisWorkbook.Worksheets("Sheet1").Cells.EntireColumn.AutoFit

'AutoFit Every Worksheet Column in a Workbook
    For Each shtIn ThisWorkbook.Worksheets
        sht.Cells.EntireColumn.AutoFit
    Next sht

End Sub

 

'SOURCE: www.TheSpreadsheetGuru.com