Ken Johnson or someone! -- I need some help again!

[Fecozisk]

Hi! and thanks for everything!
do you remember last topic "Help me! Using Excel for Soccer Games" ??
well, i have a problem on it... im using the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 46 Or Target.Row > 29 Then
Exit Sub
End If
If Target.Column < 5 Or Target.Row < 3 Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

with this i have one interval of cells being affected by the macro
perfectly, which is E4:AT29
The problem is that in the same sheet i also need to use another interval,
which is E66:AT91
....and i dont know anything about visual basic... i just copy and paste! how
should i rewrite it?
thanks!

[Bryan Hessey]

Perhaps something like

Please Login or Register  to view this content.

will help

--

Hi! and thanks for everything!
do you remember last topic "Help me! Using Excel for Soccer Games" ??
well, i have a problem on it... im using the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 46 Or Target.Row > 29 Then
Exit Sub
End If
If Target.Column < 5 Or Target.Row < 3 Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

with this i have one interval of cells being affected by the macro
perfectly, which is E4:AT29
The problem is that in the same sheet i also need to use another interval,
which is E66:AT91
....and i dont know anything about visual basic... i just copy and paste! how
should i rewrite it?
thanks!

[Stefan]

"Fecozisk" wrote:

> Hi! and thanks for everything!
> do you remember last topic "Help me! Using Excel for Soccer Games" ??
> well, i have a problem on it... im using the following:

> The problem is that in the same sheet i also need to use another interval,
> which is E66:AT91

If you have multiple ranges it's best to determine the valid fields instead
of the invalid ones:

Private Sub Worksheet_Change(ByVal Target As Range)
cm = Target.Column
rw = Target.Row
If (cm >= 5 And cm <= 46) And ((rw >= 4 And rw <= 29) Or (rw >= 66 And rw
<= 91)) Then
Me.Unprotect
Target.Locked = True
Me.Protect
End If
End Sub


In your case the columns are the same so you can test the column-range in
one time.

--
greetings from Belgium
Stefan.

[Ken Johnson]

Hi Fecozisk,

This works too...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("E4:AT29,E66:AT91"), Target) Is Nothing Then
Exit Sub
End If
If Target.Column < 5 Or Target.Row < 3 Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub


Ken Johnson

[Ken Johnson]

Hi Fecozisk,

Sorry, I should've tidied it up a bit more..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("E4:AT29,E66:AT91"), Target) Is Nothing Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

Ken Johnson

[Fecozisk]

Thanks all you guys! 100% solved!

"Ken Johnson" escreveu:

> Hi Fecozisk,
>
> Sorry, I should've tidied it up a bit more..
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Intersect(Range("E4:AT29,E66:AT91"), Target) Is Nothing Then
> Exit Sub
> End If
> Me.Unprotect
> Target.Locked = True
> Me.Protect
> End Sub
>
> Ken Johnson
>
>

[Ken Johnson]

Hi Fecozisk,

You're always welcome.

Thanks for the feedback.

Ken Johnson