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
|