前言
一张表格,密密码码的数值,千篇一律的颜色,不美观还很容易看串行。
一、Excel表格格式
套用表表格式前,最重要的一步便是选中适用表格格式区域。如果表格区域范围小,一拖一拉便可快速选中。假如适用表格样式的区域太大,单就选中表格区域就很费时,用VBA快速选中指定区域,就就变的很关键、省时。取消表格样式时,同样也需要选中适用表格样式区域,如果范围过大,就会导致区域外的合并单元格、字体等发生变化。
二、运行方法
以表格5为例,用VBA快速添加和取消表格样式(鼠标双击单元格A1套用表格格式5,鼠标右击取消表格格式)
套用表格样式
清除表格格式
二、代码如下
1.左键双击套用表格格式
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) '套用表格格式5
On Error Resume Next
Dim UsedRows As Integer
Dim UsedCols As Integer
Dim ColA As String
UsedRows = ActiveSheet.UsedRange.Rows.Count
UsedCols = ActiveSheet.UsedRange.Columns.Count
ColA = GetEngName(UsedCols)
If Target.Row = 1 And Target.Column = 1 Then
Range("A2:" & ColA & UsedRows).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$" & ColA & "$" & UsedRows), , xlYes).Name = "表5"
Range("A2:" & ColA & UsedRows).Select
ActiveSheet.ListObjects("表5").TableStyle = "TableStyleMedium1"
End If
End Sub
2.数字转字母
Public Function GetEngName(argColumn As Integer) As String '数字转英文
Dim strEngName As String
Dim iNum, iMod As Integer
iNum = argColumn \ 26
iMod = argColumn Mod 26
If (iMod = 0) Then
If (iNum = 1) Then
strEngName = Chr(90)
Else
strEngName = Chr(65 + iNum - 2) + Chr(90)
End If
Else
If (iNum = 0) Then
strEngName = Chr(65 + iMod - 1)
Else
strEngName = Chr(65 + iNum - 1) + Chr(65 + iMod - 1)
End If
End If
GetEngName = strEngName
End Function
3.右键单击取消表格格式
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) '取消表格格式
Dim UsedRows As Integer
Dim UsedCols As Integer
Dim ColA As String
UsedRows = ActiveSheet.UsedRange.Rows.Count
UsedCols = ActiveSheet.UsedRange.Columns.Count
ColA = GetEngName(UsedCols)
Range("A2:" & ColA & UsedRows).Select
Selection.ClearFormats
ActiveSheet.ListObjects("表5").TableStyle = ""
End Sub