Apply capital letter code to a range

[ipbr21054]

Within this event
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

Where the range is A2:D29

I wish to use the UCase code so anything that is type in lower case will be changed to upper case.

Please advise Thanks & have a nice day.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value anywhere in your range and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A2:D29")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

 

[ipbr21054]

Many Thanks now done.

 

[mumps]

You are very welcome.

 

[Scott Huish]

If you wanted it to capitalize all text even if it was copied and pasted into multiple cells (which is what I prefer when I do this), then use this variation:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("A2:D29"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    c = UCase(c)
Next
Application.EnableEvents = True
End Sub