C
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
*** Sent via Developersdex http://www.developersdex.com ***
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
*** Sent via Developersdex http://www.developersdex.com ***