时间戳

首页 > 解决方案 > 从条件格式返回 LineStyle

excel - 从条件格式返回 LineStyle

问题描述

我正在尝试使用结果示例来编译条件格式的详细列表。

我需要弄清楚如何返回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

标签： excelvbaconditional-formatting

解决方案

一个普通单元格最多可以有 8 个边框（从 5 = xlDiagonalDown 到 12 = xlInsideHorizo​​ntal），
但格式条件只能有 4 个边框（1 = 左，2 = 右，3 = 上，4 = 下）。

我添加了一个Iif条件以将某些值显式显示为Trueor 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

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

推荐阅读