case select and a range

[JT]

Instead of listing all of the valid values, I would like
to use a range name in a case select statement. Is this
possible. Following is the code I'm using without much
success. "VV" is the name of the range that contains the
values I want to check against.

Sub find_match()

Select Case cell

Case ("VV")
counter = counter + 0

Case Else

ActiveCell.Interior.ColorIndex = 6
counter = counter + 1

End Select

End Sub

Thanks for the help

 

[SFatz]

You won't be able to use a Select statement, but you probably can use a For
Each, depending on what you're really trying to do.

Sub find_match()
Dim rngT as Cell
For Each rngT in Range("VV")
If cell.value = rngT.value Then
counter = counter + 0
Else
ActiveCell.Interior.Color = 6
counter = counter + 1
End If
Next rngT
End Sub

Hope this helps
By the way, why bother incrementing the counter by 0?

 

[Tom Ogilvy]

Sub find_match()
counter = 0
For Each cell In Range("A1:A10")
Select Case True
Case Not IsError(Application.Match(cell, Range("VV"), 0))
counter = counter + 0
Case Else
ActiveCell.Interior.ColorIndex = 6
counter = counter + 1
End Select
Next
Range("B1") = counter
End Sub