selecting multiple noncontiguous cells 1

[ntolani]

Within VBA, is there a way to select more than one cell not located next to a previously selected cell...just like using the control button within the Excel application?

For x = 1 To Ubound(MyArray)
For y = 1 To Ubound(MyOtherArray)
If MyArray(x) = MyOtherArray Then
Cells(y, 1).Select
Format those selected Cells
End If
Next
Next

This code looks like the logic works, however obviously only the last cell that meets the conditional criteria remains selected. I need to keep all the cells that meet the criteria selected. Any info regarding this is greatly appreciated.

Thank you,
Neil

 

[sfvb]

This is assuming you're using option base 1.

myArray = Array(3, 6, 9)
myOtherArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)

mystring = ""
For x = 1 To UBound(myArray)
For y = 1 To UBound(myOtherArray)
If myArray(x) = myOtherArray Then
mystring = mystring & "A" & CStr & ","
End If
Next
Next

If Len(mystring) > 1 Then
mystring = Left(mystring, Len(mystring) - 1)
End If

Range(mystring).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 3