Make code Intersect applicable for all rows.

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

I have undermentioned code in the Private Sub Worksheet_Change(ByVal
Target As Range)

If Not Intersect(Target, Me.Range("C07")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set rng = ActiveWorkbook.Names(Target.Value).RefersToRange
Me.Range("D07").Value = rng.Offset(0, 0).Value
End If

I do have the first two columns with combo's.

The code is repreated 50 times, as I have 50 rows.

How to make it applicable for all rows, so I mentioned the code only
one time?

Bart
Ex 2003
 
Untested...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo BadCodeExists

If Not Intersect(Target, Me.Range("C07:CO56")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Dim rng As Excel.Range
Application.EnableEvents = False
Set rng = ActiveWorkbook.Names(Target.Value).RefersToRange
Target.Offset(0, 26).Value = rng.Value
End If

BadCodeExists:
Application.EnableEvents = True
End Sub
----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"AA Arens"
<[email protected]>
wrote in message
I have undermentioned code in the Private Sub Worksheet_Change(ByVal
Target As Range)

If Not Intersect(Target, Me.Range("C07")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set rng = ActiveWorkbook.Names(Target.Value).RefersToRange
Me.Range("D07").Value = rng.Offset(0, 0).Value
End If

I do have the first two columns with combo's.
The code is repreated 50 times, as I have 50 rows.
How to make it applicable for all rows, so I mentioned the code only
one time?
Bart
Ex 2003
 
Back
Top