Excel-VBA Comparing Column Data

Question

I am completely new to Excel VBA, and I need to write code to do a simple task. I need to compare values in two different columns row by row, and see if the difference exceeds 50. If so, I need to highlight the entire row.

I program in other languages, so I have a basic understanding for how this works, but I have no clue how to navigate cells/view the content inside the cells. I tried this but it didn't work out (it would just highlight every single row). I simplified it to compare if values are equal or not, but to no avail (still everything is highlighted) Can anyone give me some help?

Dim strF0_col As Integer, sF0_col As Integer
Dim myRow, counter As Integer
Dim rEnd As Integer

Sub compare_F0()
    rEnd = 100
    strF0_col = 307
    sF0_col = 317
    counter = 0

    For myRow = 2 To rEnd Step 1
        Application.StatusBar = counter & "rows highlighted."
        If (Cells(myRow, strF0_col).Value = Cells(myRow, sF0_col).Value) Then
            Cells(myRow, strF0_col).EntireRow.Interior.ColorIndex = 28
        End If
    Next myRow
End Sub

Thanks in advance

Ah I used the wrong columns to compare data, so that's what went wrong with the above code. Now I can't seem to figure out how to find the difference in the If-statement. — Spence
– Spence, Commented Jul 1, 2011 at 21:59
I tried something like: If ((Cells(myRow, "KU").Value - Cells(myRow, "LE").Value) > Abs(50)) Then... But this doesn't work like it would in C++ — Spence
– Spence, Commented Jul 1, 2011 at 21:59
Sorry, I figured everything out. I put the Abs() on the wrong side. Doing everything in a hurry right now! — Spence
– Spence, Commented Jul 1, 2011 at 22:02
You don't need VBA to do this. Conditional Formatting will handle all of this more easily (except for the status bar message). — Doug Glancy
– Doug Glancy, Commented Jul 2, 2011 at 2:24

Tiago Cardoso · Accepted Answer · 2011-07-02 03:52:31Z

2

Is there any reason to do not use Conditional Formatting, as @Doug Glancy suggested?

It worked quite fine here for me.

In case you want to give it a shoot, do as follows...

Choose the whole row
Open Conditional Formatting Menu (will depend on your Excel version. Anyway...)
Add the Rule =$KU2>$LE2+50
Set the format you want (maybe fill in yellow?)
Confirm
Copy format to other rows

Notice the row index (2, in this case) cannot have the $ symbol.

Hope it helps.

answered Jul 2, 2011 at 3:52

Tiago Cardoso

2,1061 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chris neilsen · Accepted Answer · 2011-07-01 22:07:29Z

0

You probably don't want to highlight rows that are blank?

If so, use

If Cells(myRow, strF0_col).Value <> "" And _
   Cells(myRow, strF0_col).Value = Cells(myRow, sF0_col).Value Then

As an aside, accessing cell values like this is quite slow. If you are only processing 100 rows then its fast enough, but if this number grows then you will find it slows down to a painful degree.

It is much faster to copy the range values to a variant array an then loop over that. Search SO for [Excel] "Variant Array" There are many answers that show how to do this and explain why it helps

answered Jul 1, 2011 at 22:07

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Collectives™ on Stack Overflow

Excel-VBA Comparing Column Data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related