Trouble with Arrays

[Arturo]

I’m trying to drill down a list, coloring cells that do not match any of 13
strings stored in an array. I am sure I am not using the array correctly and
in turn am looking for some direction…

Appreciatively
Arturo

Sub FalgUnMatched()
Dim X As Integer
Dim ListRef As String

ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3",
"$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9")
Range("E4").Select
For X = 4 To 8439
If ActiveCell.Value <> UBound(ListRef) Then
ActiveCell.Interior.ColorIndex = 43
End If
ActiveCell.Offset(1, 0).Select
Next X
End Sub

 

[Bob Phillips]

You are storing cell addresses in the arry, not values.

You can match a value like so

If Not IsError(Application.Match(Activecell.Value,ListRef,0)) Then

and no need to select

Sub FalgUnMatched()
Dim X As Long
Dim ListRef As String

ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3",
"$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9")
For X = 4 To 8439
If Not IsError(Application.Match(Range("E" & X).Value,ListRef,0))
Then
Range("E" & X).ColorIndex = 43
End If
Next X
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Arturo]

Sub FalgUnMatched()
Dim X As Long
Dim ListRef As Variant
Dim Counter As Integer

Counter = 0
ListRef = Array("$B$4", "$I$28", "$K$3", "$M$3", "$N$3", "$O$3", "$P$3",
"$Q$3", "$E$4", "$E$5", "$E$6", "$E$8", "$E$9")
For X = 4 To 8439
If IsError(Application.Match(Range("E" & X).Value, ListRef, 0)) Then
Range("E" & X).Interior.ColorIndex = 43
Counter = Counter + 1
End If
Next X
MsgBox Counter
End Sub