Combine Intersect Range in If statements

[Ben Dummar]

I am trying to combine the two intersect ranges below into one macro that is
in a worksheet tab. The outside if range works but the inside one doesn't.
What do I need to do to fix it?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim R As Long
R = Target.Row

On Error GoTo ErrHandler
Application.EnableEvents = True


If Not Intersect(Range("L2:m20"), Target) Is Nothing Then
If Not Intersect(Range("A2:B2"), Target) Is Nothing Then
End If

If Target.Column <> 9 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Target.Offset(0, -2).Value = Date '<<== CHECK RANGE
End If

If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Target.Offset(0, 2).Value = Date




ErrHandler:
Application.EnableEvents = True
End Sub



Thanks,

Ben

 

[Dave Peterson]

I don't understand what you're doing, but maybe this will give you an outline:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo ErrHandler

If Not Intersect(Me.Range("L2:m20"), Target) Is Nothing Then
'you're in L2:M2, so do the stuff for L2:M2 here
'some code
'some more code
Application.EnableEvents = False
'change some cells on the worksheet

ElseIf Not Intersect(Me.Range("A2:B2"), Target) Is Nothing Then
'you're in A2:B2
'do what you need
Application.EnableEvents = False
'change some different cells on the worksheet

End If

ErrHandler:
Application.EnableEvents = True
End Sub

 

[Ben Dummar]

Thanks for the help!

I was trying to have a date set in 2 different cells based upon data
changing in the corresponding 2 different rows. With your help I was able to
the macro below work. I don't know if this is the bet way but it does work.

Thanks Again!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

ActiveSheet.Unprotect Password:="password"


On Error GoTo ErrHandler


If Not Intersect(Range("B2:B5000"), Target) Is Nothing Then

If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Target.Offset(0, 2).Value = Date

Application.EnableEvents = True

ElseIf Not Intersect(Range("I2:I5000"), Target) Is Nothing Then

If Target.Column <> 9 Then Exit Sub
If Target.Row = 1 Then Exit Sub

Target.Offset(0, -2).Value = Date '<<== CHECK RANGE
End If
ActiveSheet.Protect Password:="password", DrawingObjects:=True,
Contents:=True
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

You're doing some extra stuff.

Since you're checking to see if you're in B2:B5000, there's no reason to check
to see if you're not in column 2 (B). And since you're starting with B2, you
can't be in row 1. So you don't need those checks.

Same kind of thing for I2:I5000.

And you may want to disable events before you change something on the sheet. If
you don't, then the _change event will fire when you add the date.

I'd do something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

On Error GoTo ErrHandler

If Not Intersect(Me.Range("B2:B5000"), Target) Is Nothing Then
'it's in B2:B5000, so don't bother checking
'to see if it's column B and you can't be in row 1
'since you're in B2:B5000

'unprotect the worksheet before you change the cell
Me.Unprotect Password:="password"

'turn off events to keep this change from
'making this even fire again
application.enableevents = false
Target.Offset(0, 2).Value = Date

ElseIf Not Intersect(Me.Range("I2:I5000"), Target) Is Nothing Then
'same reasoning as before. You're in column 9 and outside row 1

'unprotect the worksheet before you change the cell
Me.Unprotect Password:="password"

'turn off events to keep this change from
'making this even fire again
application.enableevents = false
Target.Offset(0, -2).Value = Date '<<== CHECK RANGE
End If

ErrHandler:
Me.Protect Password:="password", DrawingObjects:=True, Contents:=True
Application.EnableEvents = True
End Sub

 

[Ben Dummar]

Dave,

Is there a way to make it so that it only lock the worksheet when one of the
two designated columns is intersected or if it is manually set to locked. I
have manually unlocked and am trying to edit some other cells but after every
entry regardless of where the cell is it lock the worksheet.

I know I can take the password scetion out of the macro and put it back in
when I am done editing it but would prefer to not have to do that everytime.

Thanks,

Ben

 

[Dave Peterson]

If you're making the changes as a developer, I'd remove that .protect
statement. Put it back when you put it into production.

But you could look to see what you're changing:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("B2:b5000,i2:i5000")) Is Nothing Then
Exit Sub
End If

On Error GoTo ErrHandler

If Not Intersect(Me.Range("B2:B5000"), Target) Is Nothing Then
Me.Unprotect Password:="password"
Application.EnableEvents = False
Target.Offset(0, 2).Value = Date
ElseIf Not Intersect(Me.Range("I2:I5000"), Target) Is Nothing Then
Me.Unprotect Password:="password"
Application.EnableEvents = False
Target.Offset(0, -2).Value = Date
End If

ErrHandler:
Me.Protect Password:="password", DrawingObjects:=True, Contents:=True
Application.EnableEvents = True
End Sub

Dave,

Is there a way to make it so that it only lock the worksheet when one of the
two designated columns is intersected or if it is manually set to locked. I
have manually unlocked and am trying to edit some other cells but after every
entry regardless of where the cell is it lock the worksheet.

I know I can take the password scetion out of the macro and put it back in
when I am done editing it but would prefer to not have to do that everytime.

Thanks,

Ben