- 来源：互联网
- 发布时间：2026-04-02 07:57:13
在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－
