[RESOLVED] Comparing values in one column

[sveegaard]

Hi!

I really need some help to this!
I have a column in which I want to compare values. First the cell B2 need comparison with other cells and if there is a match - it colors both cells red.
Then it's B3's turn for comparison and so on. This is what I've got so far:

Sub Drwcheck()
Dim iCompare As Long
Dim iCheck As Long
Dim XL As Variant
Dim xlWS As Variant
Dim Checksum As Integer
Dim Compare As Integer
Dim Chk As Integer

Set xlWS = ActiveSheet

Dim colXLparams As New Collection
iCompare = 3
iCheck = 2
Chk = 0
Do While xlWS.Cells(iCompare, 2).Value <> ""
Checksum = Cells(iCheck, 2)
Compare = Cells(iCompare, 2)
If Checksum = Compare Then GoTo Paint Else
iCompare = iCompare + 1
Loop
Paint:
MsgBox "Fejl!!", vbCritical
With Cells(iCheck, 2).Interior
.Color = RGB(255, 0, 0)
End With
With Cells(iCompare, 2).Interior
.Color = RGB(255, 0, 0)
End With
End Sub


But it only compares the first cell :s

Help me, please

[Bartender]

Sveegard,
I'm pretty new to programming, but I think I see part of your problem.
Your code goes straight through and hits this line:

Do While xlWS.Cells(iCompare, 2).Value <> ""
Checksum = Cells(iCheck, 2)
Compare = Cells(iCompare, 2)
If Checksum = Compare Then GoTo Paint Else

If your condition is met, it jumps to here:

Paint:
MsgBox "Fejl!!", vbCritical
With Cells(iCheck, 2).Interior
    .Color = RGB(255, 0, 0)
End With
With Cells(iCompare, 2).Interior
    .Color = RGB(255, 0, 0)
End With
End Sub

And you have it exit the sub.
So it never gets to here:

iCompare = iCompare + 1
Loop

It looks like your code will run until it finds one value to paint, and then quit.

Try:

Sub Drwcheck()
Dim iCompare As Long
Dim iCheck As Long
Dim XL As Variant
Dim xlWS As Variant
Dim Checksum As Integer
Dim Compare As Integer
Dim Chk As Integer
 
Set xlWS = ActiveSheet
 
Dim colXLparams As New Collection
iCompare = 3
iCheck = 2
Chk = 0
Do While xlWS.Cells(iCompare, 2).Value <> ""
Checksum = Cells(iCheck, 2)
Compare = Cells(iCompare, 2)
If Checksum = Compare Then 
MsgBox "Fejl!!", vbCritical
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
Loop
 
End Sub

Maybe that will work... good luck!

[sveegaard]

Thanks!

I'll try your suggestion, I'll post yet a thread if it doesn't work.

[sveegaard]

Nope, it doesn't work.

It says: "Loop without do"!

Typical for MS programs - nothing works :s

[GIS_Mike]

Try throwing an End If in there right before the Loop command. It looks to me like it's considering your Loop command to be part of the preceding Else statement.

[Bartender]

Yeah, I'm sorry Sveegaard, what Mike said. I left out the End If there. Hope that works for you.

[sveegaard]

Yeah but thanks for you help anyway. Just need a little thing more (see my other thread)