Problem with Multiple Ranges in code
 

I am using excel 2000

I have a spreadsheet into which sales data is enterred, for a range of cells e120:e138, named xEuro, the data is enterred as euros and automatically converts to £

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Me.Protect UserInterfaceOnly:=True
If Not Intersect(Target, Range("e120:e138")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Round(Target / [xEuro], 0)
Target.NumberFormat = "###0,0.00"
Application.EnableEvents = True
End If
End If

End Sub

I now want to extend the range, to include 12 columns of data, but get an error message "Compile error wrong number of arguments or invalid property assignment" when I have more than 2 columns in the range

The code I have tried is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Me.Protect UserInterfaceOnly:=True
If Not Intersect(Target, Range("e120:e138", "k120:k138", "q120:q138)) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Round(Target / [xEuro], 0)
Target.NumberFormat = "###0,0.00"
Application.EnableEvents = True
End If
End If

End Sub

What do I need to change to be able to add multiple ranges

Thanks in advance for any help

Paul

Problem with Multiple Ranges in code
 

Try:

If Not Intersect(Target, Range("e120:e138,k120:k138,q120:q138")) Is Nothing Then



Paul S wrote:

I am using excel 2000

I have a spreadsheet into which sales data is enterred, for a range of
cells e120:e138, named xEuro, the data is enterred as euros and
automatically converts to £

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Me.Protect UserInterfaceOnly:=True
If Not Intersect(Target, Range("e120:e138")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Round(Target / [xEuro], 0)
Target.NumberFormat = "###0,0.00"
Application.EnableEvents = True
End If
End If

End Sub

I now want to extend the range, to include 12 columns of data, but get
an error message "Compile error wrong number of arguments or invalid
property assignment" when I have more than 2 columns in the range

The code I have tried is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
Me.Protect UserInterfaceOnly:=True
If Not Intersect(Target, Range("e120:e138", "k120:k138", "q120:q138))
Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target = Round(Target / [xEuro], 0)
Target.NumberFormat = "###0,0.00"
Application.EnableEvents = True
End If
End If

End Sub

What do I need to change to be able to add multiple ranges

Thanks in advance for any help

Paul

--
Paul S

--

Dave Peterson