Tuesday, December 12, 2006

Comparing two column's Data in Excel and coloring the Cells that differ

The following code below compares adjacent cells in 2 column's (D and E) and colors only those cells in column E that have a different data than column D :

Sub validateFields()
Dim dColumn As Variant
Dim eColumn As Variant

Set dColumn = Range("D:D")
Set eColumn = Range("E:E")

Dim cellIndex As Integer
cellIndex = 2

Do While dColumn.Cells(cellIndex).Value <> ""
If dColumn.Cells(cellIndex) <> eColumn.Cells(cellIndex) Then
With eColumn.Cells(cellIndex).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
With eColumn.Cells(cellIndex).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
End If
cellIndex = cellIndex + 1

End Sub

Private Sub Worksheet_Activate()
Call validateFields
End Sub

This assumes that the first column has the header, so, ignores it.
(cellIndex = 2 initialization)