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

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

On Tue, 16 Nov 2004 20:17:14 -0500, "CLR" wrote:

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

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



"Myrna Larson" wrote in message
...
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

On Tue, 16 Nov 2004 20:17:14 -0500, "CLR"
wrote:

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