Specific cell doesnt change color when it leaving the cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,940
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,592
Office Version
  1. 2013
Platform
  1. Windows
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

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,592
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,940
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Basically Row 6 should have the below happen

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,592
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,940
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

In post 1 it shows A6:Y6
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
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

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

Rich (BB code):
            .Interior.ColorIndex = 6
            .Font.Size = 11
            .BorderAround xlContinuous, xlThin
            .Font.Name = "Calibri"
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlVAlignCenter
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Try this in the worksheet code module"
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,152
Messages
5,640,426
Members
417,142
Latest member
andygame

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