Specific cell doesnt change color when it leaving the cell

[ipbr21054]

Afternoon,
I am using the code supplied below.
I use a command button to add a new row to my worksheet & this new row is now shown at Row 6
Clicking in each cell changes its color to green & this works fine for all the cells.
The issue is that i type in each cell A6 through to Y6 and each cell changes color as i enter the cell & also leave the cell.
All apart from cell L6 of which it does the following.
Currently blue, i enter cell & it changes to green, i type my value etc BUT when i leave the cell it changes to yellow.

In the code below i see Range("A:L,N:Y")) BUT i am bot sure what this part of the code is doing,i change it to ("A6,Y6") but just get an error message so i put it back.

I also see the part of code in BOLD but not sure what that is also doing & think that is the issue.
If i close the workbook then open it again clicking in cell L6 works correctly.

Please can you advise.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    Dim cell As Range
   
    Set Rng = Intersect(Target, Range("6:" & Rows.Count), Range("A:L,N:Y"))
   
'   Exit if nothing entered into out target range
    If Rng Is Nothing Then Exit Sub
   
'   Loop through all cells in our target range
    Application.EnableEvents = False
    For Each cell In Rng
        cell = UCase(cell)
    Next cell
    Application.EnableEvents = True
   
        If Not Intersect(Target, Range("L6:L" & Range("L" & Rows.Count).Row)) Is Nothing And Target.Cells.Count = 1 Then
   
        Application.EnableEvents = False
       
        With Target
            .Interior.ColorIndex = 6
            .Font.Size = 11
            .BorderAround xlContinuous, xlThin
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter
           
        End With
       
    End If
  End Sub

If it helps this is the selection change code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "Y"

'   *** Specify start row ***
    myStartRow = 5
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
    Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True
    

End Sub

 

[ipbr21054]

The Ucase line gives me a run time error each time,see screen shot
So for now ive just removed it and the rest of the code works correct

 

[mumps]

It works for me without any error. See if this works:

.Value = UCase(Target.Value)

 

[ipbr21054]

Thanks very much.

 

[mumps]

You are very welcome.