View Single Post
  #1   Report Post  
Paul S Paul S is offline
Junior Member
 
Posts: 24
Default 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