将条件格式宏应用于当前工作表中的表而不是命名表

Question

我记录了一个宏，每次他们弄乱时纠正表的条件格式规则(因为添加或删除行我想…)我在表格中放了一个按钮来激活宏我需要在几张表中复制相同的表(增加表数)，我希望我的宏在所有这些表上起作用(不一定同时)除了有这个共同的表，大多数表也有其他表，但会有1张表，将在大多数表中复制。(基本上创建一个模板表，其中包含用户将为每个新客户端复制的表和宏按钮由于表将具有相同数量的列和列标题，是否可以对其进行调整，使其适用于光标选择单元格的任何表?或类似的吗?也许有办法将ref从" bookingInfo "更改为" selected table "仅供参考:我根本不知道如何编写VBA

下面是我的代码:

Application.ScreenUpdating = False
Application.Goto Reference:="BookingInfo"
Selection.ListObject.Range.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$B4<>$B5"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A5<>"""""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlLightDown
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$AN5=""Full PMT"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$AN5=""Partial PMT"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False

应用程序。ScreenUpdating = True终止子

帮忙吗?

Answer 1

你可以这样做:

Sub CFUpdate()

Dim lo As ListObject, rng As Range

Set lo = Selection.ListObject
If lo Is Nothing Then 'is the selection in a listobject?
MsgBox "First select any cell in the Table to be updated", vbExclamation
Exit Sub  'nothing to do...
End If

Set rng = lo.DataBodyRange  'range to be formatted
rng.FormatConditions.Delete

With AddFC(rng, xlExpression, "=$B4<>$B5").Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With

With AddFC(rng, xlExpression, "=$A5<>""""").Interior
.Pattern = xlLightDown
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With

With AddFC(rng, xlExpression, "=$AN5=""Full PMT""").Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.399945066682943
End With

With AddFC(rng, xlExpression, "=$AN5=""Partial PMT""").Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With

Application.ScreenUpdating = True
End Sub
'factoring out some common steps
Function AddFC(rng As Range, fcType As XlFormatConditionType, frmla As String)
Dim fc As FormatCondition
Set fc = rng.FormatConditions.Add(Type:=fcType, Formula1:=frmla)
fc.StopIfTrue = False
Set AddFC = fc 'return the FormatCondition we just added
End Function

将一些常用代码提取到一个单独的函数中，以减少添加每个格式条件时的代码量。

注意，如果表格不是都从同一行开始，你还需要调整公式…