Thursday, March 25, 2010

Microsoft excel macro to compare two column for each row and highlight cell with condition

This Macro was tested with Microsoft Excel 2007.
Sub compare()
' Select first line of data.
Range("B2").Select
' Set Do loop to stop at empty cell.
Do Until IsEmpty(ActiveCell)
' Check active cell for search value.
If ActiveCell.Value = Cells(ActiveCell.Row, ActiveCell.Column - 1).Value Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value > Cells(ActiveCell.Row, ActiveCell.Column - 1).Value Then
ActiveCell.Interior.ColorIndex = 4
ElseIf ActiveCell.Value < Cells(ActiveCell.Row, ActiveCell.Column - 1).Value Then
ActiveCell.Interior.ColorIndex = 5
Else
ActiveCell.Interior.ColorIndex = 6
End If
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Every time you change a value in the cell , you have to run the macro manually. In order to run it automatically, put the script into the Worksheet_Change function. (This code was not tested , just for reference.)
Private Sub Worksheet_Change(ByVal Target As Range)
' Select first line of data.
Range("B2").Select
' Set Do loop to stop at empty cell.
Do Until IsEmpty(ActiveCell)
' Check active cell for search value.
If ActiveCell.Value = Cells(ActiveCell.Row, ActiveCell.Column - 1).Value Then
ActiveCell.Interior.ColorIndex = 3
ElseIf ActiveCell.Value > Cells(ActiveCell.Row, ActiveCell.Column - 1).Value Then
ActiveCell.Interior.ColorIndex = 4
ElseIf ActiveCell.Value < Cells(ActiveCell.Row, ActiveCell.Column - 1).Value Then
ActiveCell.Interior.ColorIndex = 5
Else
ActiveCell.Interior.ColorIndex = 6
End If
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub


A good page as reference guide is at here - http://www.ozgrid.com/VBA/run-macros-change.htm .



---

No comments:

Post a Comment

Feel free to leave your question or comment here, we will reply you as soon as possible.