Target specific range for Ucase

[ipbr21054]

Hi,

The code in use is shown below
I have added the code in red to the existing change event code but nothing happens in respect of the small case changing to upper case & i also dont receive an error message ?
I am trying to target cell range F2:G2 so when either cell has been left the Ucase will be applied

Private Sub Worksheet_Change(ByVal Target As Range)

With ThisWorkbook.Sheets("HONDA SHEET")
If Not Intersect(Target, .Range("A13")) Is Nothing And .Range("A13") <> "" Then
If Len(.Range("A13").Value) <> 17 And Len(.Range("A13").Value) <> 11 Then
                .Range("A13").Interior.ColorIndex = 3
                 MsgBox "Honda Japan Use 11 Character Vin Numbers." & vbNewLine & "" & vbNewLine & "Honda Europe Use 17 Character Vin Numbers." & vbNewLine & "" & vbNewLine & "Please Check & Try Again", vbCritical, "Chassis Number Wrong Character Count"
                .Range("A13").ClearContents
                .Range("A13").Interior.ColorIndex = 2
                .Range("A13").Activate
Else
                Application.EnableEvents = False
                .Rows(21).Insert Shift:=xlDown
                .Range("A21:G21").Borders.Weight = xlThin
                .Range("G21").Value = Date
                .Range("A21").Value = UCase(.Range("A13").Value)
                .Range("B21").Select
                .Range("A13").ClearContents
                .Range("A21").Characters(Start:=10, Length:=1).Font.ColorIndex = 3
                 Application.EnableEvents = True


End If
[COLOR=rgb(184, 49, 47)]  If Target.Address = ("F2:G2") Then
    Application.EnableEvents = False
    Target.Value = UCase(Target.Value)
    Application.EnableEvents = True
End If[/COLOR]
End If

End With

Target.Interior.ColorIndex = 6 '   *** THIS IS CELL A13 ***
If Not Intersect(Target, Range("F21")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

End If
    If Target.Address = "$F$21" Then
        Call sheettolist


End If
Application.EnableEvents = True

If Not Intersect(Target, Range("Z21")) Is Nothing Then
    Application.EnableEvents = False
    Range("Z21").Value = UCase(Range("Z21").Value)
    Application.EnableEvents = True
End If

If Not Intersect(Target, Range("C21")) Is Nothing Then
    Application.EnableEvents = False
    Range("C21").Value = UCase(Range("C21").Value)
    Application.EnableEvents = True
End If
            
End Sub

 

[davesexcel]

Use the same target address line as you used for the others.

If Not Intersect(Target, Range("F2:G2"))

Could the range be merged?

If so maybe just use F2 as the range

 

[ipbr21054]

Im sorry im not sure i followed you.

Did you mean like this ?

If Not Intersect(Target, Range("F2:G2")) Is Nothing Then
    Application.EnableEvents = False
    Range("F2:G2").Value = UCase(Range("F2:G2").Value)
    Application.EnableEvents = True
End If

 

[davesexcel]

Yes, but you can still use the target value part
You can combine them all as well with union range

       If Not Intersect(Target, Range("F2:G2")) Is Nothing Then
Application.EnableEvents = False
target.Value = UCase(target.value)
Application.EnableEvents = True
End If

 

[ipbr21054]

Thanks very much.

 

[davesexcel]

Using multiple ranges

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("F2:G2,Z21,C21,F21")) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If

End Sub