I have the following Private Sub within my relevant sheet that I'm trying to
capitalise all values entered in a Range of cells, but it returns a syntax
error on entry, I guess its got to do with the way I've typed in the ranges
in the formula, can anyone assist?

Thanks


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("E9:E37"; "I9:I37", "M9:M37",
"Q9:Q37", "U9:U37")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

If Not Intersect(.Cells, Range("E9:E37;I9:I37;M9:M37;Q9:Q37;U9:U37")) Is
Nothing Then

HTH
--
AP

"John" a écrit dans le message de news:
...
I have the following Private Sub within my relevant sheet that I'm trying
to capitalise all values entered in a Range of cells, but it returns a
syntax error on entry, I guess its got to do with the way I've typed in the
ranges in the formula, can anyone assist?

Thanks


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("E9:E37"; "I9:I37", "M9:M37",
"Q9:Q37", "U9:U37")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

Try:

If Not Intersect(Target, Range("E9:E37, I9:I37, M9:M37,Q9:Q37 , U9:U37 "))
Is Nothing Then

HTH

"John" wrote:

I have the following Private Sub within my relevant sheet that I'm trying to
capitalise all values entered in a Range of cells, but it returns a syntax
error on entry, I guess its got to do with the way I've typed in the ranges
in the formula, can anyone assist?

Thanks


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("E9:E37"; "I9:I37", "M9:M37",
"Q9:Q37", "U9:U37")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub

Thanks Guys


"Toppers" wrote in message
...
Try:

If Not Intersect(Target, Range("E9:E37, I9:I37, M9:M37,Q9:Q37 , U9:U37 "))
Is Nothing Then

HTH

"John" wrote:

I have the following Private Sub within my relevant sheet that I'm trying
to
capitalise all values entered in a Range of cells, but it returns a
syntax
error on entry, I guess its got to do with the way I've typed in the
ranges
in the formula, can anyone assist?

Thanks


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("E9:E37"; "I9:I37", "M9:M37",
"Q9:Q37", "U9:U37")) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub