在Excel中進行操作時， 很多時候都需要對儲存格進行設置， 以製作出美觀大方的表格。 在VBA中， 也可使用各種代碼對儲存格的格式進行設置。
目錄
1 用AutoFormat方法設置自動套用格式
2 用Borders集合設置邊框線
3 用HorizontalAlignment、VerticalAlignment設置文本對齊格式
4 用InsertIndent方法設置儲存格文本縮排
5 用Orientation屬性設置文本方向
6 用WrapText屬性設置自動換行格式
7 用ShrinkToFit屬性設置縮小字體填充
8 用NumberFormatLocal屬性設置日期格式
9 自訂函數生成大寫金額
10 用Pattern屬性設置儲存格圖案
11 用FormatConditions集合設置條件格式
1 用AutoFormat方法設置自動套用格式Sub 自動套用格式()
Dim rng1 As Range
Set rng1 = Sheet1.Range("A1").CurrentRegion
rng1.AutoFormat
Set rng1 = Nothing
End Sub
2 用Borders集合設置邊框線Sub 設置邊框線()
Dim rng1 As Range
Set rng1 = Sheet1.Range("A1").CurrentRegion
rng1.Borders.LineStyle = xlDouble
Set rng1 = Nothing
End Sub
3 用HorizontalAlignment、VerticalAlignment設置文本對齊格式Selection.HorizontalAlignment = xlHAlignCenter
Selection.HorizontalAlignment = xlVAlignCenter
4 用InsertIndent方法設置儲存格文本縮排Sub 增加縮排值()
On Error Resume Next
Selection.InsertIndent 1
End Sub
Sub 減少縮排值()
On Error Resume Next
Dim rng1 As Range
Set rng1 = Selection
If rng1.IndentLevel > 0 Then
rng1.InsertIndent -1
End If
Set rng1 = Nothing
End Sub
5 用Orientation屬性設置文本方向Sub 設置文本方向()
Dim i As Integer
i = Application.InputBox(prompt:="輸入文字的角度（-90～90）：", Type:=1)
If i >= -90 And i <= 90 Then
Selection.Orientation = i
End If
End Sub
6 用WrapText屬性設置自動換行格式Sub 自動換行()
Selection.WrapText = True
End Sub
7 用ShrinkToFit屬性設置縮小字體填充Sub 縮小字體填充()
Selection.ShrinkToFit = True
End Sub
8 用NumberFormatLocal屬性設置日期格式Sub 設置日期格式()
Dim rng As Range, rng1 As Range
Set rng1 = ActiveSheet.UsedRange
For Each rng In rng1
If IsDate(rng.Value) Then
rng.NumberFormatLocal = "yyyy""年""m""月""d""日"";@"
End If
Next
End Sub
9 自訂函數生成大寫金額Sub 大寫金額()
Dim t As Currency, str1 As String
Dim i As Integer, strJ As String, strF As String
Dim rng1 As Range
With ActiveSheet
Set rng1 = Range("IV1").End(xlToRight) '獲取最右側列
t = ActiveCell.Value
With rng1
.Value = t
.NumberFormatLocal = "[DBNum2][$-804]G/通用格式"
.Columns.AutoFit
str1 = .Text
.Clear
End With
i = InStr(str1, ".")
If i > 0 Then
strJ = Mid(str1, i + 1, 1) '獲取角部分字元
strF = Mid(str1, i + 2, 1) '獲取分部分字元
If strF = "" Then
str1 = Left(str1, i - 1) & "元" & strJ & "角整"
Else
str1 = Left(str1, i - 1) & "元" & strJ & "角" & strF & "分"
End If
Else
str1 = str1 & "元整"
End If
ActiveCell = "人民幣" & str1
End With
End Sub
10 用Pattern屬性設置儲存格圖案Sub 設置儲存格圖案()
Dim i As Integer
Dim r As Integer, g As Integer, b As Integer
Randomize
On Error Resume Next
For i = 1 To 18
With Selection.Interior
.Pattern = i
r = Int(Rnd * 255)
g = Int(Rnd * 255)
b = Int(Rnd * 255)
.PatternColor = RGB(r, g, b)
End With
MsgBox "下個圖案樣式"
Next i
End Sub
11 用FormatConditions集合設置條件格式Sub 設置條件格式()
Dim rng1 As Range
Set rng1 = Sheet1.Range("F3:F13")
'添加條件格式， 設置儲存格值大於等於2000的格式
With rng1.FormatConditions.Add(Type:=xlCellValue, _
Operator:=xlGreaterEqual, Formula1:=2000)
With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 6
End With
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
'添加條件格式， 設置儲存格值小於1000的格式
With rng1.FormatConditions.Add(Type:=xlCellValue, _
Operator:=xlLess, Formula1:=1000)
With .Font
.Bold = True
.ColorIndex = 10
End With
End With
End Sub
Sub 清除條件格式()
Cells.FormatConditions.Delete
End Sub
－End－