Comparing two columns in Excel VBA (greater/less t

Question

I'm actually quite surprised that I'm having so much trouble finding as answer for this. I have 2 columns containing a bunch of numbers (on the same worksheet). I simply want to have code to say "If the value in column 1 > the value in column 2, do this" for each row in the columns. I tried

If sheet.range("B2:B35").Value > sheet.range("C2:C35").Value Then
'do something
End If

But apparently it doesn't work that way.

Answer 1

You need to think about a loop to check each row independently of the others.

The idea is something like:

For i = 2 to 35
    If Sheet.Range("B" & i).Value > Sheet.Range("C" & i).Value
        'Do Something for Row i
    End If
Next

The Value can be omitted as it is implicit, meaning Sheet.Range("B" & i).Value returns an identical result as Sheet.Range("B" & i)

Additionally, there are numerous ways to address a cell depending on your needs.

  Range() 'Can be used to address multiple cells at the same time over a range
          ' or used to address a single cell as is done here

  Cells() 'Can be used to address a single Cell by calling Cells("B" & i) as is 
          ' done above, or can reference the row and column numerically like  
          ' Cells(2, i)

And either of the above methods can be used in conjunction with Offset() if you are looking to move around a given worksheet such as:

  Cells(2, 1).Offset(0, i) 'Addresses the Cell offset from "B1" by 0 columns and
                           ' i rows.  

I personally tend to use Cells(2, i) in these cases, but I just used Range as I borrowed it straight from your example code snippet.