I need some help about comparison in VBA/Excel. This is the code I've got (with a little help from bartender):
Sub Drwcheck()
Dim iCompare As Long
Dim iCheck As Long
Dim XL As Variant
Dim xlWS As Variant
Dim Checksum As Long
Dim Compare As Long
Set xlWS = ActiveSheet
Dim colXLparams As New Collection
iCompare = 3
iCheck = 2
Do While xlWS.Cells(iCompare, 2).Value <> ""
Checksum = Cells(iCheck, 2)
Compare = Cells(iCompare, 2)
If Checksum = Compare Then
With Cells(iCheck, 2).Interior
.Color = RGB(255, 0, 0)
End With
With Cells(iCompare, 2).Interior
.Color = RGB(255, 0, 0)
End With
iCompare = iCompare + 1
Else
iCompare = iCompare + 1
End If
Loop
End Sub
But it doesn't continue to the next cell and compare the rest and so on
I apologize for my reply in your other topic. It was first thing in the morning and I thought I could do some good. I didn't bother to think that if you had left out an End If, VBA would have never compiled and you would have other errors. Any how, here's my suggestion..
VB Code:
Sub Drwcheck()
Dim iCompare As Long
Dim iCheck As Long
Dim XL As Variant
Dim xlWS As Variant
Dim Checksum As Long
Dim Compare As Long
Set xlWS = ActiveSheet
Dim colXLparams As New Collection
iCompare = 3
iCheck = 2
Do While xlWS.Cells(iCheck, 2).Value <> ""
Do While xlWS.Cells(iCompare, 2).Value <> ""
Checksum = Cells(iCheck, 2)
Compare = Cells(iCompare, 2)
If Checksum = Compare Then
With Cells(iCheck, 2).Interior
.Color = RGB(255, 0, 0)
End With
With Cells(iCompare, 2).Interior
.Color = RGB(255, 0, 0)
End With
iCompare = iCompare + 1
Else
iCompare = iCompare + 1
End If
Loop
iCompare = iCheck + 2
iCheck = iCheck + 1
Loop
End Sub
You have to have a second loop to iterate through the check values. I used iCompare = iCheck + 2 and iCheck = iCheck + 1 to avoid repeat checking. This way the new check value will be the next in the list and the compare value will be one after that.
I tried iCompare = iCheck and iCheck = iCheck + 1 but the iCompare and iCheck eventually become the same value and the program compares a value to itself; everything becomes red.
Unless I'm reading the code wrong, you just want to highlight any cell with a match, right? And when there is no longer a match, you want the cell to unhighlight?
You don't need VBA for this. Just use conditional formatting.
Click on Format --> Conditional Formatting in the top cell of your column. I'll assume A1
Change the Condition to read "Cell Value Is -- Equal To" like this:
in the box to the right of that, insert this formula
=IF(COUNTIF(OFFSET($A$1,0,0,COUNTA($A:$A),1),A1)>1,A1,FALSE)
This is assuming your start cell is A1, and you are using all of column A. Obviously change $A:$A to match your range, and all A1s and $A$1s to match your top cell.
Next click the format button and select what you want your cells to look like when highlighted. Click ok.
Now highlight cell A1 and click your format painter
Use that to paint your format to the rest of the column.
Now any cell with a match should be highlighted. Change the matching number and the formatting with change back to transparent.
Forgive the explicit instructions, some may not know conditional formatting.
It is automatic, and what I'm saying is you don't even need the highlight part of the macro, you can delete it. That conditional formatting will do all the highlighting when there is a match and all unhighlighting immediately when the match is gone without using code.
Check column A in this attachment Change any of the numbers to make matches. If this is not what you're looking for then maybe I am misunderstanding.
I also changed the formula to disregard blank cells
[RESOLVED] Comparison probs
Reply With Quote
Re: Comparison probs
