如何在对向下列进行循环时，在同一行中的两个不同列中比较两个单元

Question

现在，我将代码从L2转到列中的最后一行。如果单元格值小于"以前"纸中单元格的值，则使用40的色素突出显示该单元格。

我真正想做的是能够同时循环第二列（显然它们都会始终在同一行上）。从逻辑上讲，它应该像这样：

如果单元格值（从L2开始）小于"以前"表中的单元格值，并且如果AE2处的单元格值等于"＃n/a"（注意：此时所有公式已被删除，因此"＃n/a"是字符串），然后用40的ColorIndex突出显示L2单元。

Sub Comparing()
Sheets("Output").Select
Dim UsedRng As Range, LastRow As Long
Dim x As Long
Set UsedRng = ActiveSheet.UsedRange
LastRow = UsedRng(UsedRng.Cells.Count).Row
      Range("L2").Select
      Do Until ActiveCell.Row = LastRow + 1
        If ActiveCell.Value < Range("Previous!L2") Then
            ActiveCell.Interior.ColorIndex = 40
        End If
         ActiveCell.Offset(1, 0).Select
      Loop
End Sub

编辑：感谢您的答复。经过调整后，代码可以按下以下操作：

Sub Comparing()
Dim wsOut As Worksheet
Dim wsPrev As Worksheet
Dim r As Long
Dim LastRow As Long
Set wsOut = Worksheets("Output")
Set wsPrev = Worksheets("Previous")
LastRow = wsOut.UsedRange(wsOut.UsedRange.Cells.Count).Row
For r = 2 To LastRow
    If wsOut.Cells(r, "L").Value < wsPrev.Cells(2, "L").Value And _
        Application.WorksheetFunction.IsNA(wsOut.Cells(r, "AE").Value) Then
              wsOut.Cells(r, "L").EntireRow.Delete
    End If
Next
End Sub

Answer 1

重构代码后，更改变得容易（仅对If语句进行略有更改）：

Sub Comparing()
    Dim wsOut As Worksheet
    Dim wsPrev As Worksheet
    Dim r As Long
    Dim LastRow As Long
    Set wsOut = Worksheets("Output")
    Set wsPrev = Worksheets("Previous")
    LastRow = wsOut.UsedRange(wsOut.UsedRange.Cells.Count).Row
    For r = 2 To LastRow
        If wsOut.Cells(r, "L").Value < wsPrev.Cells(r, "L").Value And _
           wsOut.Cells(r, "AE").Value = "#N/A" Then
            wsOut.Cells(r, "L").Interior.ColorIndex = 40
        End If
    Next
End Sub

---

如果Previous!L2是要专门指的是第2行，而不是要处理的行，则应在该位置将r的引用更改为2。

另外，如果AE列中的单元格包含#N/A的错误值，而不是"#N/A"的字符串（您可以告诉您尝试在Excel中进行=LEN(AE2) - 如果它返回#N/A，则AE2包含错误值，但是如果它返回4然后AE2包含一个字符串值），您可以使用IsError进行测试。

Sub Comparing()
    Dim wsOut As Worksheet
    Dim wsPrev As Worksheet
    Dim r As Long
    Dim LastRow As Long
    Set wsOut = Worksheets("Output")
    Set wsPrev = Worksheets("Previous")
    LastRow = wsOut.UsedRange(wsOut.UsedRange.Cells.Count).Row
    For r = 2 To LastRow
        If wsOut.Cells(r, "L").Value < wsPrev.Cells(2, "L").Value And _
           IsError(wsOut.Cells(r, "AE")) Then
            wsOut.Cells(r, "L").Interior.ColorIndex = 40
        End If
    Next
End Sub

Answer 2

如果我正确理解您的目标，则可能需要尝试：

Option Explicit
Sub Comparing()
    Dim cell As Range
    Dim prevVal As Double
    prevVal = Worksheets("Previous").Range("L2")
    With Worksheets("Output1")
        For Each cell In .Range("AE2:AE" & .Cells(.Rows.count, "L").End(xlUp).row).SpecialCells(xlCellTypeConstants, xlErrors).Offset(, -19)
            If cell.Value < prevVal Then cell.Interior.ColorIndex = 40
        Next cell
    End With
End Sub