如何修改Excel条件格式宏 使其作用于选中单元格所在表格而非命名表

操作步骤 #

• 打开需要设置的Excel文件，按Alt+F11快捷键调出VBA编辑器
• 在编辑器左侧的工程资源管理器里，找到你原来存放宏的标准模块双击打开；如果找不到原来的模块，就右键点击当前工作簿名称，依次选择「插入」-「模块」，新建一个空白模块
• 把模块窗口里原有旧的宏代码全部删除，粘贴下方提供的完整代码
• 按Ctrl+S保存文件，注意必须将文件保存为.xlsm（Excel启用宏的工作簿）格式，否则宏功能会失效
• 回到Excel工作表界面，右键点击你之前放的修复按钮，选择「指定宏」，在弹出的列表里选中FixBookingTableConditionalFormatting，点击确定完成绑定
• 后续制作新客户模板时，直接复制带按钮和目标表格的工作表即可，宏会随工作表一同复制，不需要重复设置

完整宏代码 #

Sub FixBookingTableConditionalFormatting()
    Dim targetTable As ListObject
    Dim tableRange As Range
    
    ' 关闭屏幕刷新减少操作卡顿
    Application.ScreenUpdating = False
    
    ' 检测当前选中单元格所属的结构化表格
    On Error Resume Next
    Set targetTable = Selection.ListObject
    On Error GoTo 0
    
    ' 未选中表格时弹出提示并终止运行
    If targetTable Is Nothing Then
        MsgBox "请先点击需要修复条件格式的表格内任意单元格，再运行本工具", vbExclamation
        Application.ScreenUpdating = True
        Exit Sub
    End If
    
    Set tableRange = targetTable.Range
    
    ' 清除表格原有全部条件格式
    tableRange.FormatConditions.Delete
    
    ' 重新写入原有条件格式规则
    ' 规则1：B列相邻行值不同时字体加粗
    tableRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$B4<>$B5"
    tableRange.FormatConditions(tableRange.FormatConditions.Count).SetFirstPriority
    With tableRange.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .TintAndShade = 0
    End With
    tableRange.FormatConditions(1).StopIfTrue = False
    
    ' 规则2：A列非空时添加浅色下斜线底纹
    tableRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5<>"""""
    tableRange.FormatConditions(tableRange.FormatConditions.Count).SetFirstPriority
    With tableRange.FormatConditions(1).Interior
        .Pattern = xlLightDown
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    tableRange.FormatConditions(1).StopIfTrue = False
    
    ' 规则3：AN列值为Full PMT时添加对应浅绿底纹
    tableRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$AN5=""Full PMT"""
    tableRange.FormatConditions(tableRange.FormatConditions.Count).SetFirstPriority
    With tableRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.399945066682943
    End With
    tableRange.FormatConditions(1).StopIfTrue = False
    
    ' 规则4：AN列值为Partial PMT时添加对应浅紫底纹
    tableRange.FormatConditions.Add Type:=xlExpression, Formula1:="=$AN5=""Partial PMT"""
    tableRange.FormatConditions(tableRange.FormatConditions.Count).SetFirstPriority
    With tableRange.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399945066682943
    End With
    tableRange.FormatConditions(1).StopIfTrue = False
    
    ' 恢复屏幕刷新
    Application.ScreenUpdating = True
    MsgBox "当前表格条件格式已修复完成", vbInformation
End Sub

注意事项 #

• 运行宏之前必须先点击目标修复表格内的任意单元格，宏只会作用于选中单元格所属的表格，不会修改同工作表内的其他表格内容
• 所有复制生成的客户表格只要列数、列标题、列顺序和原模板表完全一致，条件格式规则就会和原效果完全匹配，不需要额外调整代码
• 如果运行时弹出选中单元格的提示，只要点击目标表格内任意位置再重新点击按钮即可，不会损坏文件内容

内容的提问来源于stack exchange，提问作者K. Akl