[RESOLVED] Comparison probs

[sveegaard]

Hey

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

[GIS_Mike]

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

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.

Man, is it Friday yet?

[sveegaard]

Thanks for that!

Now it works properly - but one more thing:
How can I make the fields white/transperant again when there no longer is a match?

[mikeyc1204]

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:
http://www.milkshakers.net/images/format.jpg

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
http://www.milkshakers.net/images/painter.jpg
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.

[sveegaard]

Hi

I know that, but that's not what I'm looking for. I need it to be automatic and to be in the same macro as the highlight-part

[mikeyc1204]

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

=IF(AND(COUNTIF(OFFSET($A$1,0,0,COUNTA($A:$A),1),A1)>1,A1<>0),A1,"isFalse")

[sveegaard]

But - you need to write it in a cell, right? It has to be fool-proof, so you can't delete it by accident

[GIS_Mike]

You could try adding another With Cells(...) statement to set the color to white in the Else portion.

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
With Cells(iCompare, 2).Interior
.Color = RGB(0, 0, 0)
iCompare = iCompare + 1
End If

Is RGB (0, 0, 0,) the value for white? I don't know. You get the idea though.

I suppose that this technically changes every non-matching cell to white, but overkill never hurt anyone.

[sveegaard]

Hi all

Thanks for all your inputs - my sheet is working properly now

:) :thumb: