MrExcel Publishing
Your One Stop for Excel Tips & Solutions

help calling up another sub when targets in a certain range ( can someone let me know if this is pos


Posted by steve on March 10, 2001 10:12 AM

Can someone help me,
I'm trying to get one sub to call up another sub whenever the target cell is in a certain range(a & rw : d & rw). I then want to set a watch range and to be able to target another cell in a different range but same row(e & rw : h & rw).
Can someone let me know if this is posible.

Thanks for eany help you can provide

steve


Posted by Dave Hawley on March 10, 2001 3:01 PM

Hi steve

here is an example: In a normal module paste this:


Public Watchrange As Range, Rw As Long

Sub MyMacro()
MsgBox Watchrange.Address
End Sub

Now in the Sheet Module paste this:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
Rw = Target.Row
If Not Intersect _
(Target, Range("A" & Rw & ":" & "D" & Rw)) Is Nothing Then
Set Watchrange = Range("E" & Rw & ":" & "H" & Rw)
Run "MyMacro"
End If
End Sub


change the value in any cell in Columns A:D and you will see the address of the new watchrange.

If the sub you are wanting to run also resides in the Private Sheet Module then omit the "Run" and use simply: MyMacro

Dave


Dave


OzGrid Business Applications

Posted by steve on March 10, 2001 3:42 PM

Now in the Sheet Module paste this:

change the value in any cell in Columns A:D and you will see the address of the new watchrange. If the sub you are wanting to run also resides in the Private Sheet Module then omit the "Run" and use simply: MyMacro

Dave Dave


dave

works good i changed change to selection change, but i need to highlight the cell also before it changes the watch range.

thanks again

steve

Posted by steve on March 10, 2001 4:07 PM

Forget about calling up another macro I just need to highlight the cell selected in both ranges


dave
all I need is to highlight the first cell in the range set another range and then for it to highlight the cell selected in the second range.

I'm not sure would this be accomplished easier with multiple macros. This is why I was asking about calling another macro.

Thanks for the help

steve

Posted by Dave Hawley on March 10, 2001 4:21 PM

Re: Forget about calling up another macro I just need to highlight the cell selected in both ranges

Steve, try this then.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim Rw As Long
If Target.Cells.Count > 1 Then Exit Sub
Rw = Target.Row
If Not Intersect _
(Target, Range("A" & Rw & ":" & "D" & Rw)) Is Nothing Then
Set WatchRange = Range("E" & Rw & ":" & "H" & Rw)
Target.Interior.ColorIndex = 5
WatchRange.Cells(1, 1).Interior.ColorIndex = 5
End If
End Sub

OzGrid Business Applications

Posted by steve on March 10, 2001 4:32 PM

Its not letting me select a cell in second range

Dave

it highlights the first cell fine, but it automatically highlights the first cell in the second range, I was wanting to be able to select a cell in the second range.

thanks
steve

Posted by Dave Hawley on March 10, 2001 4:39 PM

Re: Its not letting me select a cell in second range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim Rw As Long

If Target.Cells.Count > 1 Then Exit Sub
Rw = Target.Row
If Not Intersect _
(Target, Range("A" & Rw & ":" & "D" & Rw)) Is Nothing Then
Application.EnableEvents = False
Set WatchRange = Range("E" & Rw & ":" & "H" & Rw)
Target.Interior.ColorIndex = 5
WatchRange.Cells(1, 1).Select
Application.EnableEvents = True
End If

End Sub

Dave


OzGrid Business Applications

Posted by steve on March 10, 2001 5:16 PM

Re:all it does is select the first cell in the second range

Dave

Dave all it does is select the first cell in the second range., I'm not sure but I'm thinking that it may need a second macro to allow to select a cell in the second range. What do you think.

Thanks steve

Posted by Dave Hawley on March 10, 2001 5:28 PM

Re:all it does is select the first cell in the second range

>allow to select a cell in the second range

Steve, which cell do you want to select ?

Dave
OzGrid Business Applications

Posted by steve on March 10, 2001 6:38 PM

Re:all it does is select the first cell in the second range

>allow to select a cell in the second range Steve, which cell do you want to select ? Dave

dave
Lets E1 threw H1 was selected, I now want to be able to select cell I1 or J1 or K1 or L1. I'll let you know kind off what I'm doing and maybe you might have an idea. The user of this program will first select what product they want, then they will select what style of frame they want to go along with it. The products are in columns E-H And the frame styles that corrispond to them are in columns I-L, there are also products in M-P and corrisponding frames in Q-T. What I'm trying to is make it so the user cann't pick a frame that doesn't corrispond to the product.

The highlighting is to let the user know what they selected. The thing that is being highlighted is the price of the product and then it is transferred to another cell.


Thanks steve

Here's my current code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim WatchRange As Range
Dim Rw As Long
Dim Cl As Long
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Set WatchRange = Range("E4:H31,M4:P31")
If Not Intersect(Target, WatchRange) Is Nothing Then

If Target = "N/A" Then Exit Sub

With Target
Rw = .Row
Cl = .Column
If Target.Column = Range("X" & Rw) Then
With Target
Range("E" & Rw & ":" & "H" & Rw & "," & "M" & Rw & ":" & "P" & Rw). _
Font.ColorIndex = ColorIndexNone
.Font.ColorIndex = 1
End With
Else
With Target
Range("E" & Rw & ":" & "H" & Rw & "," & "M" & Rw & ":" & "P" & Rw). _
Font.ColorIndex = ColorIndexNone
.Font.ColorIndex = 3
End With
End If
End With
If Target.Column = Range("X" & Rw) Then
With Range("X" & Rw)
Range("W" & Rw) = ""
Range("X" & Rw) = ""
End With
'End If
Else
With Range("X" & Rw)
Range("X" & Rw) = Cl
Range("W" & Rw) = Target
End With
End If

Set WatchRange = Nothing
End If

End Sub