Posted by: rajeshHamal May 20, 2009
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
'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