Test if active cell is within 2 separate unrelated ranges

[paynod]

Hi,
I am trying to change the value of cell E21 if the user selects a cell within range("B2:AC16"). If the user selects a cell within range("AE2:AF16") then I want to change the value of cell G21.

I have the following code and was trying to adopt a solution using two With statements but encountered errors. Can someone help suggest a better approach?



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim rack, issue, rackRng, issueRng As Range

Set ws = ThisWorkbook.Sheets("Sheet3")
Set rack = ws.Range("E21")
Set issue = ws.Range("g21")
Set rackRng = Intersect(ActiveCell, Range("B2:AC16"))
Set issueRng = Intersect(ActiveCell, Range("AE2:AF16"))

With issueRng
For Each c In rackRng
If Not c Is Nothing Then
rack.Value = c.Value


End If
Exit For
Next c
End With

'With rackRng
'For Each f In issueRng
' If Not f Is Nothing Then
' issue.Value = f.Value


' End If
' Exit For
'Next f

'End With

End Sub




Thanks

[mancubus]

in Worksheet_SelectionChange event, selected cell (Target) is the active cell.

do you want to change values of E21 and G21 to the selected cells' values?
if so try:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B2:AC16")) Is Nothing Then Range("E21").Value = Target.Value
If Not Intersect(Target, Range("AE2:AF16")) Is Nothing Then Range("G21").Value = Target.Value

End Sub

[SamT]

I would add to mancubus' code a new first line

If Target.count > 1 then exit sub

However it is my preference to NOT use Event Subs for any actual work. I only use them to decide which other process to run.

Starting with mancubus' offering, I get:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 then exit sub
If Not Intersect(Target, Range("B2:AC16")) Is Nothing Then E21 Target
If Not Intersect(Target, Range("AE2:AF16")) Is Nothing Then G21 Target

'If not Intersect(Target, SomeOtherRange)) Is Nothing Then SomeOtherRange Target
End Sub


Private Sub E21(Target As Range)
Range("E21").Value = Target.Value
End sub

Private Sub G21(Target As Range
Range("G21").Value = Target.Value
End sub

Private Sub SomeOtherRange(Target)
Blah, Blah
End sub


This keeps the actual Event sub clean and simple. It also keeps the code for one process from interfering with the code for another process. A little thought will reveal other advantages of this method