Macro to Change a Range

[Carlton Patterson]

Hi all,

Can someone let me know if its possible to write a macro that will allow
me to change the range where it says in the worksheet

Set Rng1 = Me.Range ("M2:M198") ?

Sometimes I only need to see range between, say M2:M20 but I don't want
to have to go into the code to chane it.

The follow worksheet module is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
Dim Rng1 As Range, Rng1A As Range, Rng1P As Range
Dim Rng2 As Range, Rng3 As Range, Rng3A As Range
Dim Rng4 As Range, Rng5 As Range, rCell As Range

Set Rng1 = Me.Range("M2:M198")

On Error Resume Next
Set Rng1A = Intersect(Target, Rng1)
Set Rng1P = Intersect(Target, Rng1.Precedents)
On Error GoTo 0

If Rng1A Is Nothing And Rng1P Is Nothing Then Exit Sub

If Not Rng1A Is Nothing Then
If Not Rng1P Is Nothing Then
Set Rng2 = Union(Rng1A, Rng1P)
Else
Set Rng2 = Rng1A
End If
Else
Set Rng2 = Rng1P
End If

Set Rng3 = Intersect(Target, Rng2)

On Error Resume Next
Set Rng3A = Rng3.Dependents
On Error GoTo 0

If Not Rng1A Is Nothing Then
If Not Rng3A Is Nothing Then
Set Rng4 = Union(Rng1A, Intersect(Rng1, Rng3A))
Else
Set Rng4 = Rng1A
End If
Else
Set Rng4 = Intersect(Rng1, Rng3A)
End If

For Each rCell In Rng4.Cells
If Not IsError(rCell.Value) Then
If rCell.Value > 1 Then
If Not Rng5 Is Nothing Then
Set Rng5 = Union(Rng5, rCell)
Else
Set Rng5 = rCell
End If
End If
End If
Next

If Rng5 Is Nothing Then Exit Sub

With Rng5
For n = 1 To 5
With .Font
If .ColorIndex = 2 Then .ColorIndex = 3 _
Else .ColorIndex = 2
End With
With .Interior
If .ColorIndex = 3 Then .ColorIndex = 2 _
Else .ColorIndex = 3
End With
Application.Wait Now + TimeValue("00:00:01")
Next

.Font.ColorIndex = 3
.Interior.ColorIndex = 2

End With

End Sub

Sometimes I don't need to see all the cells flashing between M2:M198, so
if there is a macro that can be written to allow to see a range between
say, M2:M15 that would great.

Cheers

Carlton

 

[Nick Hodge]

Carlton

Not really understanding what you mean by 'change'. You could work on a
selection

Set myRng=Selection

or if you are looking for the last cell in column M try

Dim lLastRow as Long
lLastRow=Range("M65536").End(xlUp).Row
Set myRng=Range("M2:M"&lLastRow)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Don Guillett]

you could put a number in cell d1 and use that to modify. Or, use an input
box to ask for the number.

Sub setrng()
'Set rng1 = Range("a2:a19")
Set rng1 = Range("a2:a" & Range("d1"))
rng1.Select
End Sub

 

[Norman Jones]

Hi Carlton,

Try using a named range. Then you can simply redefine your desired flash
range without touching the code.

So change:

Set Rng1 = Me.Range ("M2:M198") ?

to

Set Rng1 = Me.Range ("MyRange")

 

[Carlton Patterson]

Hi Norman,

I'm going work on Don's suggestion, in the meantime could you explain
what you mean when you mentioned, 'Then you can simply redefine your
desired flash
range without touching the code'?

Cheers

Carlton

 

[Carlton Patterson]

Hi Norman,

I made the changes as suggested but I a run-time error 1004: Method
'Range' of object'_Worksheet' failed.

Carlton

 

[Norman Jones]

Hi Carlton,

Assume that you have adapted the code to use the named rangeMyRange which
refers to M2:M198, this will be your flash range,

Changing the MyRange definition (via Insert ! Name | Define) to refer to
M2:M20, only cells in this latter range will be flashable.

In practice, given that you might wish frequently to limit (or extend) the
range of information to be monitored, it would be convenient to add a
toolbar (or sheet) button to automate range selection
..

 

[Carlton Patterson]

Hello Don,

I quite like your suggestion. I was just wondering if it could be a
little more selective? For example, is there a way that I could input in
d1 a range of cells instead of just adding a number?

Cheers

Carlton

 

[Norman Jones]

Hi Carlton,

Did you define the named range in the worksheet?

If not, wiyh the desired range selected - say M2:M20 - type MyRangw in the
Name box and hit Enter to confirm thr name.

With the name defined, the code worked for me.

 

[Carlton Patterson]

Hi Norman,

To be honest I'm not quite sure how to define a named range. Can you
show me?

Cheers

Carlton

 

[Don Guillett]

one simple way
Set rng1 = Range("" & Range("d1") & "")

 

[Bob Phillips]

Goto menu Insert>Name>Define Name ...

In the Names in Workbook box enter the name

In the refersto box input the range, preceded by =, such as =$M$1:$M$10


A simpler way is to select the range, and then type the name in the Name
Box, the dropdown to the left of the formula bar.

 

[Carlton Patterson]

Hi guys,

Thanks for the suggestions. I managed to add the code as suggested, I
now just have to wait until the market opens on Monday to put it to the
test.

Once again, thanks ever-so-much.

Cheers

Carlton