Specific cell must be Ucase advice

[ipbr21054]

Hi,

I have the code shown below.
My intention is that cell Z21 must be UPPERCASE

So typing tom jones in cell Z21 then when i leave that cell it changes to TOM JONES

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
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


[COLOR=#ff0000]Dim UpperCase As String[/COLOR]
[COLOR=#ff0000]UpperCase = Range("Z21").Value[/COLOR]


End Sub

 

[ipbr21054]

Hi
I’m out and was doing it from memory.
You are correct it is HONDA SHEET

 

[Joe4]

OK. Then if everything is on the same sheet, then if you move the code we created to the top of your code, I believe it should work.

Note. I think what through people off is the reference to the sheet name in your code. Since this is a Worksheet event procedure code that, by default, runs against the sheet whose module it is located in, it isn't necessary to specify the sheet name in the code (unless you were trying to do something to a different sheet).

 

[RoryA]

I click next to this line, If Not Intersect(Target, Range("Z21")) Is Nothing Then
I now see the red dot on the left.
I go to the sheet and wfrite test in cell Z21 then leave the cell.
I am taken back to the code where i now see the code line above yellow
I then F8 and it goes straight to END IF then END SUB then the is gone.

Then the cell that changed to trigger this code wasn't Z21.

 

[Joe4]

Z21 isn't part of a merged cell, is it?

 

[ipbr21054]

Hi,
Just returned and the reply from Joe4 at the begining is correct / working.

Dont have a clue as to where Z came from because it was C

Many thanks all working now.