Specific cell doesnt change color when it leaving the cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,905
Office Version
  1. 2007
Platform
  1. Windows
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.




Rich (BB code):
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.

Rich (BB 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

Well-known Member
Joined
Nov 16, 2010
Messages
3,905
Office Version
  1. 2007
Platform
  1. Windows
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
 

Attachments

  • 1165.jpg
    1165.jpg
    31.4 KB · Views: 2

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
It works for me without any error. See if this works:
VBA Code:
.Value = UCase(Target.Value)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,464
Messages
5,624,885
Members
416,064
Latest member
PaulBr2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top