Ucase code for specific range

[ipbr21054]

Hi,
I have the code shown below which changes small case to upper case.

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub


    On Error Resume Next


    If Not Intersect(Target, Range("G13:O51")) Is Nothing Then


        Application.EnableEvents = False


        Target = UCase(Target)


        Application.EnableEvents = True


    End If


    On Error GoTo 0


End Sub

The range G13:O51 pretty much controls the whole sheet.
Can you advise the edit should i require the range L13:L18 & G21:G23 to not be included & stay as small case

 

[Rick Rothstein]

What should happen if the target (which can be a multi-cell range) overlaps part of the range you want to upper case (this can occur when someone copy/pastes a multi-cell range of values)?

 

[ipbr21054]

I understand what you have advised and thanks.
The cell range in question will have text input into it and that’s it.

Nothing will be pasted into the cells in question & if anything does happen in the cells it will only be to reword the text.

Thanks.

 

[Rick Rothstein]

Okay, give this code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
    If Not Intersect(Target, Range("H13:K18,M13:O18,H19:O23,G13:G20,G24:O51")) Is Nothing Then
      Application.EnableEvents = False
      Target = UCase(Target)
      Application.EnableEvents = True
    End If
End Sub

 

[ipbr21054]

Hi,
That has allowed me to do what i require but im a little lost as to how it did it.
I mean L13:L18 & G21:G23 are not even mentioned in the code just ranges around it ?

could you explain please.

 

[Rick Rothstein]

Hi,
That has allowed me to do what i require but im a little lost as to how it did it.
I mean L13:L18 & G21:G23 are not even mentioned in the code just ranges around it ?

could you explain please.

The code tests for the intersection of the Target cell and the range of cells you want it to be able to change and allows them to be changed... there is no reason to test the cells that are to be ignored.

 

[ipbr21054]

Thanks