从条件格式返回LineStyle

Question

我正在尝试编译一个条件格式的详细列表，并给出一个结果示例。

我需要弄清楚如何在行样式上返回True或False。或者，如果可能的话，返回行样式名称(如xlContinuous)。并将其应用于第11细胞。

注释是工作的一部分，如果它能帮助任何人。

Sub CompileConditionalFormattingList()
    Dim i As Long, cSh As Worksheet, nSh As Worksheet
    Set cSh = ActiveSheet
    Application.ScreenUpdating = False
    Set nSh = Worksheets.Add(After:=cSh)
    With nSh
        .Name = "Format Report"
        .Cells(1, 1).Resize(, 11).Value = _
        Array("Formula", "Interior Color", "Font Color", "Bold", "Italic", "B.Top", "B.Bottom", "B.Left", "B.Right", "Number Format", "Format")

        For i = 1 To cSh.Cells.FormatConditions.Count
            '.Cells(i + 1, 1).Value = "'" & cSh.Cells.FormatConditions(i).Formula1
            '.Cells(i + 1, 2).Value = cSh.Cells.FormatConditions(i).Interior.Color
            '.Cells(i + 1, 3).Value = cSh.Cells.FormatConditions(i).Font.Color
            '.Cells(i + 1, 4).Value = cSh.Cells.FormatConditions(i).Font.Bold
            '.Cells(i + 1, 5).Value = cSh.Cells.FormatConditions(i).Font.Italic
            .Cells(i + 1, 6).Value = cSh.Cells.FormatConditions(i).Borders(xlEdgeTop).LineStyle ' I want this to return the line style
            .Cells(i + 1, 7).Value = cSh.Cells.FormatConditions(i).Borders(xlEdgeBottom).LineStyle ' I want this to return the line style
            .Cells(i + 1, 8).Value = cSh.Cells.FormatConditions(i).Borders(xlEdgeLeft).LineStyle ' I want this to return the line style
            .Cells(i + 1, 9).Value = cSh.Cells.FormatConditions(i).Borders(xlEdgeRight).LineStyle ' I want this to return the line style
            '.Cells(i + 1, 10).Value = cSh.Cells.FormatConditions(i).NumberFormat
            With .Cells(i + 1, 11)
                '.Value = "Abc123"
                '.Interior.Color = cSh.Cells.FormatConditions(i).Interior.Color
                '.Font.Color = cSh.Cells.FormatConditions(i).Font.Color
                '.Font.Bold = cSh.Cells.FormatConditions(i).Font.Bold
                '.Font.Italic = cSh.Cells.FormatConditions(i).Font.Italic
                .Borders(xlEdgeTop).LineStyle = cSh.Cells.FormatConditions(i).Borders(xlEdgeTop).LineStyle 'Here I want the line style to be replicated
                .Borders(xlEdgeBottom).LineStyle = cSh.Cells.FormatConditions(i).Borders(xlEdgeBottom).LineStyle 'Here I want the line style to be replicated
                .Borders(xlEdgeLeft).LineStyle = cSh.Cells.FormatConditions(i).Borders(xlEdgeLeft).LineStyle 'Here I want the line style to be replicated
                .Borders(xlEdgeRight).LineStyle = cSh.Cells.FormatConditions(i).Borders(xlEdgeRight).LineStyle 'Here I want the line style to be replicated
                '.NumberFormat = cSh.Cells.FormatConditions(i).NumberFormat
            End With
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Answer 1

一个正常的单元可以有多达8个边框(从5= xlDiagonalDown到12 = xlInsideHorizontal)，

但是格式条件只能有4个边框(1 =左，2=右，3=顶，4=底部)。

我添加了一个Iif条件，将一些值显式地显示为True或False。

另外，我将ColorIndex设置为未填充的单元格，否则将显示为黑色内部。

Sub CompileConditionalFormattingList()
    Dim i As Long, cSh As Worksheet, nSh As Worksheet
    Set cSh = ActiveSheet
    Application.ScreenUpdating = False
    Set nSh = Worksheets.Add(After:=cSh)
    With nSh
        .Name = "Format Report"
        .Cells(1, 1).Resize(, 11).Value = _
        Array("Formula", "Interior Color", "Font Color", "Bold", "Italic", _
            "B.Left", "B.Right", "B.Top", "B.Bottom", "Number Format", "Format")

        For i = 1 To cSh.Cells.FormatConditions.Count
            .Cells(i + 1, 1).Value = "'" & cSh.Cells.FormatConditions(i).Formula1
            .Cells(i + 1, 2).Value = cSh.Cells.FormatConditions(i).Interior.Color
            .Cells(i + 1, 3).Value = cSh.Cells.FormatConditions(i).Font.Color
            .Cells(i + 1, 4).Value = IIf(cSh.Cells.FormatConditions(i).Font.Bold, True, False)
            .Cells(i + 1, 5).Value = IIf(cSh.Cells.FormatConditions(i).Font.Italic, True, False)
            .Cells(i + 1, 6).Value = GetLinestyleName(cSh.Cells.FormatConditions(i).Borders(1).LineStyle)
            .Cells(i + 1, 7).Value = GetLinestyleName(cSh.Cells.FormatConditions(i).Borders(2).LineStyle)
            .Cells(i + 1, 8).Value = GetLinestyleName(cSh.Cells.FormatConditions(i).Borders(3).LineStyle)
            .Cells(i + 1, 9).Value = GetLinestyleName(cSh.Cells.FormatConditions(i).Borders(4).LineStyle)
            .Cells(i + 1, 10).Value = cSh.Cells.FormatConditions(i).NumberFormat
            With .Cells(i + 1, 11)
                .Value = "Abc123"
                .Interior.Color = cSh.Cells.FormatConditions(i).Interior.Color
                .Interior.ColorIndex = cSh.Cells.FormatConditions(i).Interior.ColorIndex
                .Font.Color = cSh.Cells.FormatConditions(i).Font.Color
                .Font.Bold = cSh.Cells.FormatConditions(i).Font.Bold
                .Font.Italic = cSh.Cells.FormatConditions(i).Font.Italic
                .Borders(xlEdgeLeft).LineStyle = cSh.Cells.FormatConditions(i).Borders(1).LineStyle
                .Borders(xlEdgeRight).LineStyle = cSh.Cells.FormatConditions(i).Borders(2).LineStyle
                .Borders(xlEdgeTop).LineStyle = cSh.Cells.FormatConditions(i).Borders(3).LineStyle
                .Borders(xlEdgeBottom).LineStyle = cSh.Cells.FormatConditions(i).Borders(4).LineStyle
                .NumberFormat = cSh.Cells.FormatConditions(i).NumberFormat
            End With
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Private Function GetLinestyleName(i As Long) As String
    Select Case i
    Case Excel.XlLineStyle.xlContinuous     ' 1
        GetLinestyleName = "xlContinuous"
    Case Excel.XlLineStyle.xlDash           ' -4115
        GetLinestyleName = "xlDash"
    Case Excel.XlLineStyle.xlDashDot        ' 4
        GetLinestyleName = "xlDashDot"
    Case Excel.XlLineStyle.xlDashDotDot     ' 5
        GetLinestyleName = "xlDashDotDot"
    Case Excel.XlLineStyle.xlDot            ' -4118
        GetLinestyleName = "xlDot"
    Case Excel.XlLineStyle.xlDouble         ' -4119
        GetLinestyleName = "xlDouble"
    Case Excel.XlLineStyle.xlLineStyleNone  ' -4142
        GetLinestyleName = "xlLineStyleNone"
    Case Excel.XlLineStyle.xlSlantDashDot   ' 13
        GetLinestyleName = "xlSlantDashDot"
    Case Else
        GetLinestyleName = "unknown"
    End Select
End Function

如果希望看到格式条件的更多参数，可以通过以下方式将其赋值给变量：

Dim fc as FormatCondition
...
Set fc = cSh.Cells.FormatConditions(i)
Stop

如果之后停止代码，则可以在本地窗口中检查其参数。