Alternate Looping method

[Lenard]

Hi,

After several attempts, there is no result for 2nd method using Each Cell For Next Loop based on the following codes.


Option Explicit
Sub CompareCells()
' Looping method using Counter For Next Loop
Dim i As Integer
Calculate
For i = 1 To Range("New").Cells.Count
If Range("New").Cells(i) > Range("Old").Cells(i) Then
Range("New").Cells(i).Interior.Color = vbYellow
Else
Range("New").Cells(i).Interior.Color = vbCyan
End If
Next i
End Sub


Sub CompareCells1()
' Alternative Looping method by using Each Cell For Next Loop
Dim Cell As Range
Calculate
For Each Cell In Range("New")

If Range("New").Range(Cell.Address) > Range("Old").Range(Cell.Address) Then
Range("New").Range(Cell.Address).Interior.Color = vbYellow
Else
Range("New").Range(Cell.Address).Interior.Color = vbCyan
End If
Next Cell

End Sub

Any help will be much appreciated as I'm VBA beginner

Thanks in Advance

Regards
Len

 

[gregtx81]

It doesn't really make sense to use a for loop to compare the cells of two ranges as the cells of only one range are being looped. You could go with the following, but i prefer your original code which uses symmetric notation and is thus more readable, imo.

Sub CompareCells
Dim Cell As Range
Calculate
i=0
For Each Cell In Range("New")
i=i+1
If cell.value > Range("Old").cells(i).value Then
cell.Interior.Color = vbYellow
Else
cell.Interior.Color = vbCyan
End If
Next Cell

End Sub

Now, if you know the range "Old" is, say, one cell to the left of "New", it makes sense to go with the following (where "Old" is never explicitly reference):

Sub CompareCells

Dim Cell As Range
Calculate

For Each Cell In Range("New")

If cell.value > cell.offset(0,-1).value Then
cell.Interior.Color = vbYellow
Else
cell.Interior.Color = vbCyan
End If
Next Cell

 

[Lenard]

Hi

Thanks for your reply and your codes
Now, I'm getting to know better the use of For Next Loop....


Regards
Len