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

Post a Comment

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

Popular posts from this blog

Setup mail server with ldap authentication in docker

-
Getting an email server to run in docker is easy but getting an email server with ldap authentication to run in docker spend me a week to get up and running. Here are the steps that I taken. I will assume you already know how to install docker. Install LDAP. docker run \              --detach \              --restart unless-stopped \              --name openldap \              -e LDAP_ORGANISATION=domain \              -e LDAP_DOMAIN=domain.com \              -e LDAP_ADMIN_PASSWORD=complex-password \              -e LDAP_RFC2307BIS_SCHEMA=true \              -e LDAP_REMOVE_CONFIG_AFTER_SETUP=true \              -e LDAP_TLS=false \              -p 389:389 \              --volume /data/openldap/var_lib_ldap:/var/lib/ldap \              --volume /data/openldap/etc_ldap_slapd.d:/etc/ldap/slapd.d \              osixia/openldap:latest Install postfix-book schema into the ldap. # cd /etc/ldap/schema # apt update # apt install vim wget # wget https://raw.githubusercontent.com/variablenix/ldap-
Read more

How to allow non root user to execute hping command ?

-
If you run hping command without root permission or without sudo and you receive this error : [open_sockraw] socket(): Operation not permitted [main] can't open raw socket This command might be able to help you. sudo setcap cap_net_raw+ep /usr/bin/hping3 (or any location of the hping command) setcap command is to set file capabilities. What are filesystem capabilities?    For the purpose of performing permission checks, traditional Unix implementations distinguish two categories of processes: privileged processes (whose effective user ID is 0, referred to as superuser or root), and unprivileged processes (whose effective UID is non-zero). Privileged processes bypass all kernel permission checks, while unprivileged processes are subject to full permission checking based on the process's credentials (usually: effective UID, effective GID, and supplementary group list).  Starting with kernel 2.2, Linux divides the privileges traditionally associated with super
Read more

Fedora 19 with kernel 3.10 fail to install vmplayer module.

-
If you are having problem start up vmware player on Fedora 19 after upgrade kernel to 3.10, here is the solution. Basically it is due to the procfs interface and vmnet not able to compiles. First download the procfs patch from http://communities.vmware.com/thread/446113?start=0&tstart=0 .If you don't have time to read the forum, here is the link of the procfs patch. Or you can use the following command: # tar xf /usr/lib/vmware/modules/source/vmnet.tar # cd vmnet-only # wget  http://communities.vmware.com/servlet/JiveServlet/download/2239207-108590/procfs.patch # patch -p1 < procfs.patch # cd .. # tar -cvf vmnet.tar vmnet-only/ # cp vmnet.tar /usr/lib/vmware/modules/source/ This will patch the procfs interface. Secondly download the vmnet patch from http://mysticalzero.blogspot.com/2013/07/vmblock-patch-for-linux-310-vmware.html . The link of the patch is here . Or you can use the following command: # tar xf /usr/lib/vmware/modules/source/vmblock.tar
Read more