multiple values in an array

I'm having a thick moment!!
Basically, i have two columns of numbers. Column a is unique and column J has multiples of column a (though not all column a values match). I need to match and colour each corresponding value. The attached spreadsheet and macro works but i'm not sure how to increase the array so it looks for each value. Some of the spreadsheets i have to check are in excess of 100K rows. Ouch!

AB33,
I have 2 problems with this. For some reason the 'Rng.Interior.ColorIndex = myColor(I)' throws up an error of 'Runtime 9 'out of range'
the second part is my own fault. Can it be modified so that that only values of 100 in Col D are coloured, i had forgotten about that when I send in the post.

many thanks in anticipation.

PeterJ

Does it help.... Peterjohns..

Just let me.. know...

Vikas gautam

Sub Test()
Set ColA = Range("A1:A100")
Set ColJ = Range("J1:J100")
For Each Cell1 In ColA
    For Each Cell2 In ColJ
        If Cell1.Value = Cell2.Value Then
          Cell1.Interior.ColorIndex = 6
          Cell2.Interior.ColorIndex = 6
        End If
    Next
Next

End Sub

Vikas,
yes it does work. many thanks.

however, in Col D this is a hierarchal value, can be 10, 20, 30, 40, 50 or 100.
Would it be easy to modify your solution so that it only works on those with a value of a 100?

My fault I forgot that rather relevant point.

Okay! I can correct that error. As it is now, your code colours columns A and J.
"Can it be modified so that that only values of 100 in Col D are coloured?"
Do you mean if the rows in column J and A match and if the value if column D is 100, colour that cell?
There is no row where the match is found and column D value is 100. Or am I missing something?

Originally Posted by PeterJohns

Column a is unique and column J has multiples of column a (though not all column a values match). I need to match and colour each corresponding value.
...
Some of the spreadsheets i have to check are in excess of 100K rows. Ouch

Peter,

If this quote does indeed accurately describe your dataset, and you do have 100K+ rows, and want to bypass rows with 100 in col D, then you may like to try the following

Sub color_matches_x()

Dim rsa&, rsj&, u&
Dim a, j, d
Dim adra As String, adrj As String
Dim b As Object, c As Object
Set b = CreateObject("scripting.dictionary")
Set c = CreateObject("scripting.dictionary")
rsa = Cells(Rows.Count, 1).End(3).Row
rsj = Cells(Rows.Count, "j").End(3).Row

a = Range("A1:A" & rsa)
j = Range("J1:J" & rsj)
d = Range("D:D").Value

For u = 2 To rsa
    If (d(u, 1) = 100) * (Len(a(u, 1)) > 0) Then b(a(u, 1)) = True
Next u

For u = 2 To rsj
    If (d(u, 1) = 100) * (Len(j(u, 1)) > 0) Then
        c(j(u, 1)) = True
        If b(j(u, 1)) Then
            If Len(adrj & ",J" & u) > 254 Then
                Range(Mid(adrj, 2)).Interior.Color = vbYellow
                adrj = ",J" & u
            Else
                adrj = adrj & ",J" & u
            End If
        End If
    End If
Next u

For u = 2 To rsa
    If (d(u, 1) = 100) * (Len(a(u, 1)) > 0) Then
        If c(a(u, 1)) Then
            If Len(adra & ",A" & u) > 254 Then
                Range(Mid(adra, 2)).Interior.Color = vbYellow
                adra = ",A" & u
            Else
                adra = adra & ",A" & u
            End If
        End If
    End If
Next u

Range(Mid(adra, 2)).Interior.Color = vbYellow
Range(Mid(adrj, 2)).Interior.Color = vbYellow

End Sub