paynod
03-16-2018, 05:10 AM
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
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