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 .



---

Comments

Popular posts from this blog

Setup mail server with ldap authentication in docker

Install VMware workstation 11 on Fedora 21 with kernel 3.17

How to allow non root user to execute hping command ?