Find value, then Create a Range

[CLR]

Hi All..........

If someone would be so kind.........is it possible for a macro to do a
"find" in column A for the value in M1, and then if found, create a
NamedRange using the word RANGEand concatenating the value in M1 on the end
of it, and causing the range thereby created to be 14 rows below and 4
columns to the right of the found cell, including the found cell.

If so, here's what I would really like to do:
Say M1=30210 (greater than zero), then when the macro starts, search for
30210 in column A (say it's found in A4), copy that value down the next 14
rows, then create a range from A4:E18 and name it RANGE30210.......then,
apply a heavy black border around the range and color it's cells
yellow.......terminate without looking for a second occurance.

If 30210 is not found in column A, then just color M1 RED and terminate the
macro.


TIA for any help
Vaya con Dios,
Chuck, CABGx3

 

[Myrna Larson]

I haven't tested this, so it may be buggy...

Sub FindIt()
With ActiveSheet
Set M1 = Range("M1")
Set Rng = .Range("A:A").Find(M1.Value)
End With

If Rng Is Nothing Then
With M1
'put code here to set the color and borders
End With
Else
Set Rng = Rng.Resize(15, 5)
With Rng
.Name = "RANGE" & M1.Value
'put code here to set the color and borders
End With
End If
End Sub

 

[CLR]

Really really COOL Myrna.............thank you VERY much.............your
code solves the major problems and works just dandy, and is so simple, even
I can understand most of it.........

Thanks muchly again,
Vaya con Dios,
Chuck, CABGx3

I haven't tested this, so it may be buggy...

Sub FindIt()
With ActiveSheet
Set M1 = Range("M1")
Set Rng = .Range("A:A").Find(M1.Value)
End With

If Rng Is Nothing Then
With M1
'put code here to set the color and borders
End With
Else
Set Rng = Rng.Resize(15, 5)
With Rng
.Name = "RANGE" & M1.Value
'put code here to set the color and borders
End With
End If
End Sub