Arrays

Z

Zootrot

Excel 2000

How do I:

1. load a list of data into an array;
2. check whether cells in another list are contained in
that array (highlighting cells which are not)

Thanks.
 
P

Pete McCosh

There's almost certainly a better, more efficient and
cleverer way to do this, but this code reads a list of
values in column A into an array, then compares every
value in column B against that list and makes the cell
bold if it's not a match.

Sub InArray()

Dim MyArray() As String
Dim X As Integer, Y As Integer
Dim NumEle As Integer
Dim Lastrow As Integer

NumEle = Application.WorksheetFunction.CountA_
(ActiveSheet.Columns(1))

ReDim MyArray(NumEle)

For X = 1 To NumEle
MyArray(X) = Cells(X, 1).Value
Next X

Lastrow = Range("B65536").End(xlUp).Row

For X = 1 To Lastrow
Cells(X, 2).Font.Bold = True
For Y = 1 To NumEle
If Cells(X, 2).Value = MyArray(Y) Then
Cells(X, 2).Font.Bold = False
End If
Next Y
Next X

End Sub

' Cheers, Pete
 
Top