Select multiple cells in a range

[YasserKhalil]

Hello everybody
I want to find and select multiple cells in a range using worksheet_selectionchange

In Range("I2") I have avalue ..
I want to use vba to find this value in the range ("A1:H50") as a whole

I want the code to be in Worksheet_Change so as to when typing a new value in I2 the cells that match this value to be selected

Thanks advanced

[Vidya Palani]

Try this..


Private Sub Worksheet_Change(ByVal Target As Range) Dim a As Long
If Not Intersect(Target, Range("I2")) Is Nothing Then
Range("I2").Select
a = Selection.Value
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
End If
End Sub

[YasserKhalil]

Thanks for your reply
I have error (Type mismatch)

[Vidya Palani]

What type of value is in cell I5?
You might have to change the data type in the Dim statement accordingly. If that doesn't work, please share a sample file.

[YasserKhalil]

Thanks for your reply
I have error (Type mismatch)
The range I2 data type is a number
And The range to be searched (A1:H50)

[Vidya Palani]

Try this. Have changed the range to "A1:H50"

Private Sub Worksheet_Change(ByVal Target As Range) Dim a As Long
If Not Intersect(Target, Range("I2")) Is Nothing Then
Range("I2").Select
a = Selection.Value
Range("A1:H50").Select
Selection.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
End If
End Sub

[YasserKhalil]

The same error at the same line


a = Selection.Value
Thanks for your try

[Vidya Palani]

Weird, the only way for this error to show up is if the Data type in Cell I2 is anything other than a number.
Can you possibly upload a sample sheet?

[YasserKhalil]

Weird, the only way for this error to show up is if the Data type in Cell I2 is anything other than a number.
Can you possibly upload a sample sheet?

The code works now but I want to find the whole number not partial
example when I entered 51 in I2 the selection was for 151
second I want to select all the cells of that value
third when entering a number not in the range there is an error12445

[mancubus]

there are multiple occurences of the same value. then what?
after selecting the cell what will you do?
there are both values and formulas in the cells; so choose xlValues.

below will look for the I2 value after A1.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$I$2" Then Exit Sub
Range("A1:H10").Find(Target.Value, Cells(1), xlValues, xlWhole, xlByRows, xlNext).Select
End Sub