时间戳

首页 > 解决方案 > 使用 VBA 将函数/代码应用于工作表中的每一行

excel - 使用 VBA 将函数/代码应用于工作表中的每一行

问题描述

我有一个宏，它将条件格式应用于突出显示特定值的行我想知道是否有办法将它应用于多行（我希望它从第 18-79 行运行）正如您从下面的代码中看到的那样，该功能必须单独调整每一行（我已经完成了 3 个）。我想知道是否有更简单的方法来应用它而不是重复并调整我需要的所有行。

Sub Highlight()
'
' Highlight good values

Application.ScreenUpdating = False

    Dim ws As Worksheet


    For Each ws In ActiveWorkbook.Worksheets

    ws.Activate


    Rows("18:18").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C$18", Formula2:="=$D$18"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Rows("19:19").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C$19", Formula2:="=$D$19"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Rows("20:20").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C$20", Formula2:="=$D$20"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Next ws

Application.ScreenUpdating = True

End Sub

标签： excelvba

解决方案

更改范围以包含所有内容：

With ActiveSheet.Rows("18:79")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$C18", Formula2:="=$D18"
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16752384
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

推荐阅读