Macro to compare two columns and highlight differences

kaleonard08

New Member
Joined
Mar 9, 2016
Messages
8
Hello,

HELP PLEASE! THANKS IN ADVANCE!

I want to run a macro that compares two columns on a looong table and highlights the difference.
I need to compare Column EAU (Sheet3, Column E, Rows 1-4210 (and growing) - called EAU) to Column Qty (Sheet3, Column I, same rows - called Qty). The values in each column are numbers.

What I am looking for:
  • I want to hit a button to run the macro for an unknown number of rows (keep going until it runs out of rows to compare).
  • I need it to highlight (or anything I can use to filter the column) the both cells if the "Qty" column is less than the "EAU" column.

Example: The italicized cells below would be highlighted as the Qty column is less than the EAU column. The others would be fine as they meet the requirement.
EAU
QTY
15,00020,000
28
1
200
50
921,230

<tbody>
</tbody>

  • Also, I need it to skip past the empty rows as there are a lot of duplicate rows which I deleted and there are now some blank rows.

Hopefully, I've explained this well enough but if not PLEASE LET ME KNOW IF YOU HAVE ANY QUESTIONS.

THANKS AGAIN FOR ANY HELP!!! :biggrin::biggrin::biggrin:
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,829
Hi kaleonard08,

This will do the job:

Code:
Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim wsMySheet As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsMySheet = ThisWorkbook.Sheets("Sheet3")

    For Each rngMyCell In wsMySheet.Range("E2:E" & wsMySheet.Range("E" & Rows.Count).End(xlUp).Row)
        If Val(rngMyCell.Offset(0, 4)) < Val(rngMyCell) Then
            wsMySheet.Range("E" & rngMyCell.Row & ":I" & rngMyCell.Row).Interior.Color = RGB(255, 255, 0)
        Else
            wsMySheet.Range("E" & rngMyCell.Row & ":I" & rngMyCell.Row).Interior.Color = xlNone
        End If
    Next rngMyCell
    
    Set wsMySheet = Nothing
    
    Application.ScreenUpdating = True

End Sub

Any entries where the value in Col. I is less than the value in Col. E will colour columns E to I of that row yellow (change to suit).

Regards,

Robert
 
Last edited:

Forum statistics

Threads
1,148,108
Messages
5,744,873
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top