I have this formula for forcing capitals to be entered into column C:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
..Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub

Could anybody help me configure it so I can do this for Column D and E
as well? (I.E. Column's 4 and 5)


---
Message posted from http://www.ExcelForum.com/

Hi Ian!

Try:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 And Target.Column <= 5 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
Target.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Ian,

Replace your statement :
If Target.Column = 3 Then
with :

If Target.Column 3 And Target.Column < 6 Then

Your routine as it is now doesn't replace the input with the capital.
To accomplish you should also replace :
Value = UCase(.Value)
with :
..Value = UCase(.Value)
( NB the . before the first Value)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"ianripping " wrote in message
...
I have this formula for forcing capitals to be entered into column C:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub

Could anybody help me configure it so I can do this for Column D and E
as well? (I.E. Column's 4 and 5)


---
Message posted from http://www.ExcelForum.com/

Sorry 3 should be 2

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"A.W.J. Ales" wrote in message
...
Ian,

Replace your statement :
If Target.Column = 3 Then
with :

If Target.Column 3 And Target.Column < 6 Then

Your routine as it is now doesn't replace the input with the capital.
To accomplish you should also replace :
Value = UCase(.Value)
with :
.Value = UCase(.Value)
( NB the . before the first Value)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

"ianripping " wrote in message
...
I have this formula for forcing capitals to be entered into column C:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Column = 3 Then
For Each cell In Target.Cells
With cell
If Not .HasFormula And Not IsNumeric(.Value) Then
Application.EnableEvents = False
Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
Next cell
End If
End Sub

Could anybody help me configure it so I can do this for Column D and E
as well? (I.E. Column's 4 and 5)


---
Message posted from http://www.ExcelForum.com/