提问人:2mas 提问时间:11/15/2023 最后编辑:Mayukh Bhattacharya2mas 更新时间:11/15/2023 访问量:29
循环浏览工作表(并应用条件格式)
Loop through worksheets (and apply conditional formatting)
问:
这个子不能正确地循环工作表,我错过了什么?
我知道格式规则可以做得更好,但它必须像这样,以便用户可以将自己的规则快速复制/粘贴到代码中(宏记录器 -> 以特定方式创建新的条件格式规则 (!) ->停止宏。然后宏吐出所有条件格式规则的代码!
循环不起作用。我没有收到任何错误消息。
Sub Test()
Dim rg As Range
Dim i As Integer, j As Integer
Dim wb As Workbook: Set wb = ThisWorkbook
i = wb.Worksheets.Count
Application.ScreenUpdating = False
'COPY CODE BETWEEN "OBERES ENDE" and "UNTERES ENDE"
For j = 6 To i
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------- OBERES ENDE --------------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
' only one as example
Columns("A:K").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$K1=50%"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------- UNTERES ENDE --------------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Next j
wb.Save
Application.ScreenUpdating = True
End Sub
答:
2赞
CDP1802
11/15/2023
#1
无需,使用Selection
With
Option Explicit
Sub Test()
Dim wb As Workbook, j As Integer
Set wb = ThisWorkbook
Application.ScreenUpdating = False
For j = 6 To wb.Worksheets.Count
With wb.Sheets(j).Columns("A:K")
.FormatConditions.Add Type:=xlExpression, Formula1:="=$K1=50%"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 192, 0) ' 49407
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With
Next
Application.ScreenUpdating = True
wb.Save
End Sub
评论
0赞
2mas
11/20/2023
就我而言,选择是必要的,我什至解释了原因!请更仔细地阅读问题。
评论
wb.Sheets(j).Activate
For j = 6 To i
Columns("A:K")