Microsoft Excel - compare between two columns row by row using macro

1. Select the cell/Range (say A1:B10). Please note that the cell reference A1 mentioned in the formula is the active cell in the selection. Active cell will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=$B1=$A1+1

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format. Enter the formula in the box below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For Each cell In Range("$B1:$B7")

If cell.Value = Sheet1.Cells(cell.Row, 1).Value Then

cell.EntireRow.Interior.ColorIndex = 40

Else

cell.EntireRow.Interior.ColorIndex = Default

End If

Next

End Sub