Advice for existing code edit please

[ipbr21054]

Morning,

This code works how it should with no problems.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim My_Range As Range
    
    Dim LastRow As Long
    
    Set My_Range = Worksheets("COLOR CELLS").Range("A1:Z50")
    
    For Each cell In My_Range
    
    If cell.Value Like "MONDAY" Then
        cell.Interior.ColorIndex = 3
        
    ElseIf cell.Value Like "TUESDAY" Then
        cell.Interior.ColorIndex = 4
        
    ElseIf cell.Value Like "WEDNESDAY" Then
        cell.Interior.ColorIndex = 22
        
    ElseIf cell.Value Like "THURSDAY" Then
        cell.Interior.ColorIndex = 6
        
    ElseIf cell.Value Like "FRIDAY" Then
        cell.Interior.ColorIndex = 7
        
    ElseIf cell.Value Like "SATURDAY" Then
        cell.Interior.ColorIndex = 8
        
    ElseIf cell.Value Like "SUNDAY" Then
        cell.Interior.ColorIndex = 46
        
    Else
        cell.Interior.ColorIndex = xlNone
        
    End If
    
    Next
    
End Sub


However i would like to know how to edit it to not restrict it to the current range shown of which is A1:Z50

I have done the below for it to hopefully work using the xlUp function but im getting deeper into it now not working but popping up error messages

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim My_Range As Range
    
    Dim LastRow As Long
    
    With Sheets("COLOR CELLS")
    
    If cell.Value Like "MONDAY" Then
        cell.Interior.ColorIndex = 3
        
    ElseIf cell.Value Like "TUESDAY" Then
        cell.Interior.ColorIndex = 4
        
    ElseIf cell.Value Like "WEDNESDAY" Then
        cell.Interior.ColorIndex = 22
        
    ElseIf cell.Value Like "THURSDAY" Then
        cell.Interior.ColorIndex = 6
        
    ElseIf cell.Value Like "FRIDAY" Then
        cell.Interior.ColorIndex = 7
        
    ElseIf cell.Value Like "SATURDAY" Then
        cell.Interior.ColorIndex = 8
        
    ElseIf cell.Value Like "SUNDAY" Then
        cell.Interior.ColorIndex = 46
        
    Else
        cell.Interior.ColorIndex = xlNone
        
    End If
    
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    
    End With
    
End Sub

 

[Dazzawm]

Why post the same problem in two different threads?

 

[ipbr21054]

It isnt the same problem.

One i would like to see how it can be wrote to cover part of the sheet without mentioning a range.

The other to allow upper / lower case

So two different questions.

 

[Dazzawm]

They are both linked and can be covered in the one.

 

[ipbr21054]

Ok then please advise and hightlite the added code to show which covers each of my questions.

 

[mohadin]

Like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim My_Range As Range
    Dim LastRow As Long
    With ActiveSheet
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    End With
    
    Set My_Range = Worksheets("COLOR CELLS").Range("A1:Z" & LastRow)
    
    For Each cell In My_Range
    
    If cell.Value Like "MONDAY" Then
        cell.Interior.ColorIndex = 3
        
    ElseIf cell.Value Like "TUESDAY" Then
        cell.Interior.ColorIndex = 4
        
    ElseIf cell.Value Like "WEDNESDAY" Then
        cell.Interior.ColorIndex = 22
        
    ElseIf cell.Value Like "THURSDAY" Then
        cell.Interior.ColorIndex = 6
        
    ElseIf cell.Value Like "FRIDAY" Then
        cell.Interior.ColorIndex = 7
        
    ElseIf cell.Value Like "SATURDAY" Then
        cell.Interior.ColorIndex = 8
        
    ElseIf cell.Value Like "SUNDAY" Then
        cell.Interior.ColorIndex = 46
        
    Else
        cell.Interior.ColorIndex = xlNone
        
    End If
    
    Next
    
End Sub

 

[ipbr21054]

Hi,
I dont need to mention a range.
I need it to just apply to any part of the sheet so not mentioning A1:Z etc

 

[My Aswer Is This]

Why are you using selection change and not change?
This means the code runs anytime you select a cell and not just make a change to the cell.

 

[ipbr21054]

An oversight & now changed thanks

 

[My Aswer Is This]

An oversight & now changed thanks

Are we just dealing with cell change in column A
Or the entire sheet?