excel - VBA 条件格式打印格式

Question

我正在尝试列出工作簿中的所有条件格式规则，并在其他地方找到以下代码。该子例程列出了工作表的名称、应用的公式和应用的范围。我有两个问题：

1. 如何添加一个额外的列来显示格式的外观？

2. 有没有办法让我更改 Excel 选项卡中的规则并将它们刷新到条件格式规则管理器？如何保持其中的顺序？

Sub List_Conditional_Formatting_Rules()

' Print all conditional formatting rules in the all worksheets
    Application.ScreenUpdating = False

    Dim ws As Worksheet, wsCF As Worksheet, NR As Long
    Dim CFrule As FormatCondition, Rng As range

    On Error Resume Next

    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "CF Rules"   'add sheet
    Set wsCF = Sheets("CF Rules")
    wsCF.range("A1:C1").Value = [{"Sheet","Formula","Range"}]

    NR = 2      'define starting row

    For Each ws In Worksheets
     If ws.Name <> "CF Rules" Then
        Set Rng = ws.Cells

        For Each CFrule In Rng.FormatConditions
            wsCF.range("A" & NR).Value = ws.Name                    ' rule names
            wsCF.range("B" & NR).Value = "'" & CFrule.Formula1      ' formula names
            wsCF.range("C" & NR).Value = CFrule.AppliesTo.Address   ' applied area

            ' want to add a column to show how the format actually looks like

            NR = NR + 1
        Next CFrule
    End If

    Next ws

    wsCF.Columns.AutoFit
    Application.ScreenUpdating = False
    MsgBox ("All conditional formatting rules are printed.")

End Sub

请让我知道是否有任何文件可以阅读以更好地理解这一点。谢谢！

标签： excelvbaexcel-2010