循环浏览工作表(并应用条件格式)

Loop through worksheets (and apply conditional formatting)

提问人:2mas 提问时间:11/15/2023 最后编辑:Mayukh Bhattacharya2mas 更新时间:11/15/2023 访问量:29

问:

这个子不能正确地循环工作表,我错过了什么?

我知道格式规则可以做得更好,但它必须像这样,以便用户可以将自己的规则快速复制/粘贴到代码中(宏记录器 -> 以特定方式创建新的条件格式规则 (!) ->停止宏。然后宏吐出所有条件格式规则的代码!

循环不起作用。我没有收到任何错误消息。

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
Excel VBA 循环 工作表

评论

3赞 CDP1802 11/15/2023
“我错过了什么?” 后wb.Sheets(j).ActivateFor j = 6 To i
0赞 2mas 11/20/2023
为什么我必须在此处激活工作表?通常。激活并没有真正使用 iirc。你还有进一步的阅读吗?
0赞 CDP1802 11/20/2023
如果你添加那行,它有效吗?
0赞 2mas 11/20/2023
确实如此,直到现在我才开始测试它!您有关于我上面评论的问题的任何信息吗?
0赞 CDP1802 11/20/2023
没有工作表引用是默认/活动工作表。遍历 j 并不能改变这一点。与类似Columns("A:K")

答:

2赞 CDP1802 11/15/2023 #1

无需,使用SelectionWith

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
就我而言,选择是必要的,我什至解释了原因!请更仔细地阅读问题。