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

 

[My Aswer Is This]

Sounds like to me you received this code from someone and are not sure what it's doing.
Why not tell us what you want and we will write you some code to do exactly what you want.
And you said: I use a command button to add a new row
We could have that done without clicking a button.

 

[ipbr21054]

The code should do what’s it written all for Row 6
I don’t even see why A L N Y is even there as all cells should changed to the colour mentioned and upper case.

 

[My Aswer Is This]

So why was A L N Y put in the code.
Again I asked for you to tell me what you want to happen when you do what?

 

[ipbr21054]

I dont know why but maybe at the time column M was not to be touched ???

Basically Row 6 should have the below happen

            .Interior.ColorIndex = 6
            .Font.Size = 11
            .BorderAround xlContinuous, xlThin
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter
           
           All must be UCASE

 

[My Aswer Is This]

You said:
Basically Row 6 should have the below happen
well row 6 has about 1,500 cells
And when what happens should this happen?

 

[ipbr21054]

In post 1 it shows A6:Y6

 

[mumps]

It looks like you are going around in circles. Rather than trying to decipher your code, I agree with @My Answer Is This, that you should describe in detail what you want to do referring to specific cells, ranges, rows, columns and sheets without reference to the code you posted.

 

[ipbr21054]

Row 6 is generated.
I then type in the cells from A to Y at row 6

Sometimes the font, text etc etc isnt what it should be so the below codew will force it to be the same all the time.

I need the code to make sure all cells are UCASE & The code should be applied for cells A7:Y6

            .Interior.ColorIndex = 6
            .Font.Size = 11
            .BorderAround xlContinuous, xlThin
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter

 

[mumps]

Try this in the worksheet code module"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A6:Y6")) Is Nothing Then Exit Sub
    With Target
        .Value = UCase(.Value)
        .Interior.ColorIndex = 6
        .Font.Size = 11
        .BorderAround xlContinuous, xlThin
        .Font.Name = "Calibri"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
    End With
End Sub