Posted by: rajeshHamal May 20, 2009
excel experts help
Login in to Rate this Post:     0       ?        
'compares a to d, b to e.. like that
'but it is more generic
'you can select any range of data
'as long as columns count is even

Option Explicit
Option Base 1
Sub sajha2()
    Dim c As Long
    Dim r As Long
    Dim leftCell As Range
    Dim rightCell As Range
    Dim leftRange As Range
    Dim rightRange As Range
    Dim myRange As Range
    Set myRange = Selection
       myRange.Interior.ColorIndex = xlColorIndexNone
    Dim colCount As Long
    Dim rowCount As Long
    colCount = myRange.Columns.count
    rowCount = myRange.Rows.count

    Dim topLeft As Range
    Dim botRight As Range
    
    'create two data ranges
    'left part
    Set topLeft = myRange.Cells(1, 1)
    Set botRight = myRange.Cells(rowCount, colCount / 2)
    Set leftRange = Range(topLeft, botRight)
    
    'right part
    Set topLeft = myRange.Cells(1, colCount / 2 + 1)
    Set botRight = myRange.Cells(rowCount, colCount)
    Set rightRange = Range(topLeft, botRight)
    
    'processing
    For r = 1 To rowCount
        For c = 1 To leftRange.Columns.count
            Set leftCell = leftRange.Cells(r, c)
            Set rightCell = rightRange.Cells(r, c)
            If leftCell.Value = rightCell.Value Then
                leftCell.Interior.Color = vbGreen
                rightCell.Interior.Color = vbGreen
                
            End If
        Next c
    Next r
End Sub
Read Full Discussion Thread for this article