Macro to compare two columns and highlight differences

[kaleonard08]

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.
​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]EAU
[/TD]
[TD]QTY
[/TD]
[/TR]
[TR]
[TD]15,000[/TD]
[TD]20,000[/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]92[/TD]
[TD]1,230[/TD]
[/TR]
</tbody>[/TABLE]
​

• 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!!!

 

[Trebor76]

Hi kaleonard08,

This will do the job:

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

 

[kaleonard08]

Worked like a charm!!! Thank you!!!

 

[Trebor76]

You're welcome. Thanks for rep's