Comparing two data sets

[Slammedtgs]

Hello everyone. I have what I hope is a simple question. I have been playing around with VBA more and more lately because I now use excel almost all day.

I am trying to compare two sets of data, column(s) H and J. The range is unknown because there is a different number of rows depending on the month being reviewed.

Currently my macro is getting an error when it is first executed - though I can continue and it worked fine (I think). Using Debug the error occurs at the "End if"

Can anyone let me know if I am coding this poorly, or what I might do other than On Error Resume to cure the problem. BTW, this code is not entirely mine, I got the idea from a Google search.

I the future I will do more with the matched data but for now I just want to get the logic down.

Thanks in advance!

Sub Compare2()

Application.StatusBar = ""

x = 0
Vlue = 0
Range("h1").Select
Set SampleSet = Range(ActiveCell, ActiveCell.End(xlDown))
SampleSet.Interior.ColorIndex = xlNone
SampleSet.Font.ColorIndex = 0

Range("j1").Activate
Set CompareSet = Range(ActiveCell, ActiveCell.End(xlDown))
CompareSet.Interior.ColorIndex = xlNone
CompareSet.Font.ColorIndex = 0

For Each ApData In SampleSet
    For Each ArData In CompareSet
    x = x + 1
            If ApData = ArData Then
            'Vlue = Vlue + ArData.Value
                With ApData.Interior
                .ColorIndex = 15
                .Pattern = xlSolid
                End With
                ApData.Font.ColorIndex = 5
                With ArData.Interior
                .ColorIndex = 15
                .Pattern = xlSolid
              End With
                ArData.Font.ColorIndex = 5
            [COLOR="Blue"] [B] End If [/B][/COLOR]
    Next ArData
Next ApData
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.StatusBar = ("Complete...   " & Format(x, "#,###.##"))

End Sub

 

[capparfrank]

First, tell us what the error is

It looks like working code to me. I dropped into excel 2007 and it executed fine. No errors.

 

[Slammedtgs]

Using Debug the error occurs at the "End if"

The error is at the End If - Highlighted in blue. I think it might be my machine, it works correctly (the way its written) on another computer.

However, It doesn't work the way I would like it to. For example, if there is a list in column H (1) and a list in column J (1,1,1,1) the code will highlight each of the 1's in column J. Vlue = 4 - I want it to be 1

I tried an if statement that would exit for if ArData.ColorIndex = 15. It worked, but it would completly stop, rather than just skipping the value that was already found. I dont want to count duplicates. Can anyone give me an idea to start with to skip a cell in column J if its ColorIndex = 15.

Thanks again