Find And Select Multiple Cells

[YasserKhalil]

Hello every body

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

 

[YasserKhalil]

I need this please

 

[YasserKhalil]

Just want to select the multiple cells that match the value in I2

 

[YasserKhalil]

I want a solution please

 

[YasserKhalil]

I want a solution please

UP

 

[YasserKhalil]

In Range("I2") I have avalue ..
I want to use <acronym title="visual basic for applications">vba</acronym> 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

I think my request is clear .. If it is difficult tell me to stop posting ?

 

[YasserKhalil]

Patience ! May be someone would be kind and reply

 

[L. Howard]

Hi YasserKhalil,

Do you really need to SELECT the cells, or would highlighting them be okay?

If selection is what you must have what will you do with the SELECTION?

Howard

 

[YasserKhalil]

Yes sir I want to select all the matching cells not highlighting them

 

[L. Howard]

Hi YasserKhalil,

Give this a try.

Regards,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'/by[B] Claus MS Public Prog[/B].

If Intersect(Target, Range("I2")) Is Nothing Then Exit Sub
Application.EnableEvents = False

If IsNumeric(Target.Value) = False Then
   Range("I3").Select
   MsgBox "Must be number!"
   Range("I2").ClearContents
   Application.EnableEvents = True
   Exit Sub
End If

Dim rngBig As Range, rngC As Range
Dim aNum As Long
aNum = Range("I2")
For Each rngC In ActiveSheet.Range("A1:H50")
   If rngC.Value = aNum And rngBig Is Nothing Then
      Set rngBig = rngC
   ElseIf rngC = aNum And Not rngBig Is Nothing Then
         Set rngBig = Union(rngBig, rngC)
   End If
Next

Application.EnableEvents = True
rngBig.Select

End Sub