ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
This code works how it should with no problems.
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
This code works how it should with no problems.
Rich (BB code):
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
Rich (BB code):
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