代码表格制作

如何利用Excel VBA代码设置表格样式？

本节内容

纯代码设置单元格样式：字体、字号、字体颜色、背景颜色、隔行填充背景颜色、金额格式等。

效果图如下：

纯代码设置表格样式

代码

‘//// 设置报表样式 ////

‘表头

With Cells(h - 2, 1)

.Value = "供货商报表"

.Resize(1, 4).Merge ‘合并四列

.Font.Color = RGB(82, 129, 185) ‘字体颜色

.Font.Name = "新宋体" ‘字体

.Font.Size = 20 ‘字号

.Font.Bold = True ‘字体加粗

.RowHeight = 40 ‘行高

.HorizontalAlignment = xlCenter ‘居中

End With

‘报表行高、列宽

Cells(h + 1, 1).Resize(dic.Count, 1).RowHeight = 18

Columns(1).ColumnWidth = 4.5

Columns(2).ColumnWidth = 40

Columns(3).ColumnWidth = 15

Columns(4).ColumnWidth = 10

‘报表标题栏

With Cells(h, 1).Resize(1, 4)

.HorizontalAlignment = xlCenter ‘居中

.Font.Color = RGB(255, 255, 255) ‘字体颜色

.Font.Bold = True ‘字体加粗

.Font.Size = 12 ‘字号

.Interior.Color = RGB(82, 129, 185) ‘底色

.RowHeight = 23 ‘行高

.Borders.Color = RGB(184, 206, 254) ‘边框颜色

End With

‘序号列

With Cells(h + 1, 1).Resize(dic.Count, 1)

.HorizontalAlignment = xlCenter ‘居中

End With

‘数据区隔行填充颜色

Dim n As Integer

For n = 1 To dic.Count

n = n + 1

With Range("A" & n + h & ":" & "D" & n + h)

.Interior.Color = RGB(218, 231, 240) ‘背景颜色

.Borders(xlEdgeBottom).Color = RGB(184, 206, 254) ‘下边框颜色

.Borders(xlEdgeTop).Color = RGB(184, 206, 254) ‘上边框颜色

End With

Next n

‘设置金额列格式

With Range(Cells(h + 1, 3), Cells(h + 1 + dic.Count, 3))

.NumberFormat = "#,##0.00;-#,##0.00"

End With

‘设置“合计金额”四个字

With Cells(h + 2 + dic.Count, 2)

.Value = "合计金额"

.Font.Bold = True ‘字体加粗

.HorizontalAlignment = xlRight ‘字体居右

End With

‘设置总金额格式

With Cells(h + 2 + dic.Count, 3)

.Font.Color = vbRed ‘字体颜色

.Font.Name = "Arial Black" ‘字体名称

.Font.Size = 13 ‘字号

.Font.Bold = True ‘字体加粗

.NumberFormat = "￥#,##0.00;-#,##0.00"

End With