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

Install VMware workstation 11 on Fedora 21 with kernel 3.17

-
Image
Here is some solutions which might be able to help you during your VMware workstation 11 installation on Fedora 21 with kernel 3.17. 1. When you try to power on a virtual guest, you received error : "Unable to change virtual machine power state internal error". Change the option mks.enable3d in the .vmx file from TRUE to FALSE . mks.enable3d = FALSE 2. When you try to power on a virtual guest, you received error: "Version mismatch with vmmon module: expecting xxx.x, got yyy.y. You have an incorrect version of the 'vmmon' kernel module. Try command below, these steps will help to patch vmnet source code: # curl http://pastie.org/pastes/9934018/download -o /tmp/vmnet-3.19.patch # cd /usr/lib/vmware/modules/source # tar -xf vmnet.tar # patch -p0 -i /tmp/vmnet-3.19.patch # tar -cf vmnet.tar vmnet-only # rm -rf *-only # vmware-modconfig --console --install-all 3.  When you try to power on a virtual guest, you received error: "Failed to open device
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