comparing cells with array

Question

i want to compare the values in the array taken from a certain column with values of another column

but i am getting an error "subscript is out of range" is there a better way of doing this?

Dim start As Integer
Dim SrchRngzc As Range, cel As Range, SrchRngyx As Range, cel2 As Range
Set SrchRngzc = Range("zc16:zc500")
Set SrchRngyx = Range("yx16:yx100")
Dim x As Integer, a As Integer, b As Integer, c As Integer
Dim y As Integer
Dim n As Integer
Dim arr(1 To 85) As String
Dim num(1 To 85) As Integer

y = 1
c = 1

'highlight cells that matches
For Each cel In SrchRngyx
    arr(y) = cel.Value
    y = y + 1
Next cel

For Each cel2 In SrchRngzc
    n = 1
    For c = 1 To y
        If arr(n) = cel2.Value Then  ' error occurs here
            cel2.Interior.ColorIndex = 4
            n=n+1
            Exit For
        End If
    Next c
Next cel2

Answer 1

The code below has 1 For to loop through all cells in column "ZC", and then per cell checks if there is a match somewhere in column "YC", by using the Application.Match.

Code

Option Explicit

Sub MatchColumns()

Dim SrchRngzc As Range, Cel As Range, SrchRngyx As Range

Set SrchRngzc = Range("ZC16:ZC500")
Set SrchRngyx = Range("YX16:YX100")

' loop thorugh cells in column "ZC"
For Each Cel In SrchRngzc
    ' check if courrent value in column "ZC" has a match in column "YX"
    If Not IsError(Application.Match(Cel.Value, SrchRngyx, 0)) Then
        Cel.Interior.ColorIndex = 4
    End If
Next Cel

End Sub

Answer 2

You have set y to 86 at the end of your first For . . . Next loop. When you try to access arr(86) you get your error. Instead try

y=0

For Each cel in SrchRngyx
   y = y+ 1
   arr(y) = cel.value
Next

This still starts at 1 but ends at 85.