Reputation: 1
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
Upvotes: 0
Views: 2569
Reputation: 2107
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...
=$KU2>$LE2+50
Notice the row index (2, in this case) cannot have the $ symbol.
Hope it helps.
Upvotes: 2
Reputation: 53136
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
Upvotes: 0