VBA将不同工作表的格式条件转换为循环

Question

我正在寻找一种方法将以下格式条件转换为循环以提高编码效率。我想过将 Table 放在数组中，但重复的表名似乎没有太大改进。

条件格式非常简单。它只是突出显示值介于 5 到 10 和 -5 到 -10 之间的单元格。

任何见解都会有所帮助！

With y.Sheets("Table 1").Range("P:P").FormatConditions.Add (Type:=xlCellValue, Operator:=xlBetween, Formula1:="=5", Formula2:="10")
.Interior.Color = rgbYellow
 End With
With y.Sheets("Table 1").Range("P:P").FormatConditions.Add (Type:=xlCellValue, Operator:=xlBetween, Formula1:="=-5", Formula2:="-10")
.Interior.Color = rgbYellow
 End With
With y.Sheets("Table 2").Range("P:P").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=5", Formula2:="10")
.Interior.Color = rgbYellow
 End With
With y.Sheets("Table 2").Range("P:P").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=-5", Formula2:="-10")
.Interior.Color = rgbYellow
 End With
With y.Sheets("Table 3").Range("P:P").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=5", Formula2:="10")
.Interior.Color = rgbYellow
 End With
With y.Sheets("Table 3").Range("P:P").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=-5", Formula2:="-10")
.Interior.Color = rgbYellow
 End With
With y.Sheets("Table 7").Range("P:P").FormatConditions.Add (Type:=xlCellValue, Operator:=xlBetween, Formula1:="=5", Formula2:="10")
.Interior.Color = rgbYellow
 End With
With y.Sheets("Table 7").Range("P:P").FormatConditions.Add (Type:=xlCellValue, Operator:=xlBetween, Formula1:="=-5", Formula2:="-10")
.Interior.Color = rgbYellow
 End With

Answer 1

尝试：

Sub test()
Dim xl_sheet As Worksheet
For Each xl_sheet In ActiveWorkbook.Worksheets
    If xl_sheet.Name Like "Table*" Then
    With xl_sheet
    .Range("P:P").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=5", Formula2:="10").Interior.Color = rgbYellow
    .Range("P:P").FormatConditions.Add(Type:=xlCellValue, Operator:=xlBetween, Formula1:="=-5", Formula2:="-10").Interior.Color = rgbYellow
    End With
    End If

 Next xl_sheet
End Sub