How to compare two cells in two different columns in the same row while looping down column?

Question

Right now I have the code going from L2 and looping down to the last row in the column. If the cell value is less than the value of a cell from the "Previous" sheet then that cell is highlighted with the ColorIndex of 40. Works great.

What I really want to do though is to be able loop through a second column at the same time (they will both be on the same row at all times obviously). Logically it should go like this:

If the cell value (beginning at L2) is less than the value of a cell from the "Previous" sheet AND if the cell value at AE2 is equal to "#N/A" (note: at this point all formulas have been removed so the "#N/A" is a string) THEN highlight the L2 cell with the ColorIndex of 40. Any help is appreciated.

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

EDIT: Thanks for the replies. After tweaking things got the code to work as follows:

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

You should [Avoid Select/Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for starters. That will make it easier to just add another check for the second column. — Rdster, Dec 23 '16 at 18:49

YowE3K · Accepted Answer · 2016-12-23T21:26:53.593

1

After refactoring your code the change becomes easy (just a slight change to the If statement):

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

If Previous!L2 is meant to refer specifically to row 2, and not to the row being processed, the reference to r should be changed to 2 in that spot.

Also, if the cell in column AE contains an error value of #N/A and not a string of "#N/A" (you can tell be trying to do =LEN(AE2) in Excel - if it returns #N/A then AE2 contains an error value, but if it returns 4 then AE2 contains a string value), you can test for that using 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

edited Dec 23 '16 at 21:26

answered Dec 23 '16 at 19:26

YowE3K

23,852
7
26
40

Tried this out and (to a beginner) it seems to be valid but I get a "Runtime Error (13): Type Mismatch" when it hits the "For" loop. It's not clear to me what the mismatch is. – lookininward Dec 23 '16 at 19:57
Think I should clarify: `For r = 2 To LastRow ` `If wsOut.Cells(r, "L").Value >= Range("Previous!L2").Value Then` `wsOut.Cells(r, "L").Interior.ColorIndex = 40` `End If` `Next ` The L in Output and the AE in Output are going down row by row. However, they are only matching against a specific value which in in Previous!L2. When I modify it like above it works well, however when I add: `And wsOut.Cells(r, "AE").Value = "#N/A"` it hits the same Runtime Error again. – lookininward Dec 23 '16 at 20:24
1

@lookininward So you want the equivalent of Previous!L$2? If so, I will update the answer to change r in that spot. Are you sure the value in column AE has been changed to a string value of `"#N/A"`, and isn't still an error value? – YowE3K Dec 23 '16 at 21:19
Yup, exactly. Using `< wsPrev.Cells(2, "L").Value1` works, however there is still the issue of the Runtime Error 13 when hitting `And wsOut.Cells(r, "AE").Value = "#N/A"`. I'm sure that the cells in Column AE are formatted correctly. I checked it manually and tested it using "General" and "Text" formatting and received the same error. – lookininward Dec 23 '16 at 21:48
1

@lookininward You can test whether cell AE2 contains an error value or text by doing `=LEN(AE2)` in Excel - if it returns #N/A then AE2 contains an error value, but if it returns 4 then AE2 contains a string value. If it contains an error value, use the updated code I put in my answer about 30 minutes ago. – YowE3K Dec 23 '16 at 22:04
You're right, it does return an error... even though I formatted it as text. However, inside that same cell formatted as text if I type in something like "Hello" then the `=LEN(AE2)` returns 5, the length of the string. Is #N/A reserved? – lookininward Dec 23 '16 at 22:15
@lookininward "#N/A" is not reserved, but I doubt whether you typed "#N/A" into the cell. My guess is that you copied a cell containing a `#N/A` error code, and that means the new cell (even if you did a paste values) would still contain a `#N/A` error code. – YowE3K Dec 23 '16 at 22:24
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131423/discussion-between-lookininward-and-yowe3k). – lookininward Dec 24 '16 at 19:25
Thanks again for your help. I was able to get it working with this added `Application.WorksheetFunction.IsNA(wsOut.Cells(r, "AE").Value)` – lookininward Dec 24 '16 at 21:51

score 0 · Answer 2 · answered Dec 24 '16 at 00:11

if I correctly understand your goal you may want to try this:

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

@lookininward, did you try this? – user3598756 Dec 27 '16 at 09:28 — user3598756, Dec 27 '16 at 09:28

How to compare two cells in two different columns in the same row while looping down column?

2 Answers2