VBA ucase code question

[ipbr21054]

Evening.
I have this working code below

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 14 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End Sub

I would like to have the upper case text only apply to the range G13:O42

I then input this line Range("G13:O42").Value = UCase(.Value) so code now shown below but what i found was what if i wrote DOG in cell L31 the word DOG would be inserted into every cell within the range.

Private Sub Worksheet_Change(ByVal Target As Range)    With Target
        If .Column = 14 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            Range("G13:O42").Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[PatOBrien198]

Try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Range("G13:o42")) Is Nothing Then
   With Target
        If .Column = 14 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End If
End Sub

 

[ipbr21054]

Hi,
I would say that had no affect,whether i type small or upper case it stays the same as i type.
My aim is within the range whatever i type will be upper case but outside the range will be lower case.

Thanks

 

[Scott Huish]

If a value was copied into more than one cell in that range, the macro would not run. I would write it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("G13:O42"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If c.Column <> 14 Then
            If Not c.HasFormula Then c = UCase(c)
        End If
    Next
Application.EnableEvents = True
End Sub

 

[ipbr21054]

Many thanks Scott,
Worked perfect.

All the best