Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colours not changing
Hi everyone,
The following code is used to change the colour in a cell after a given (validated) entry. In the next cell the date of the change is written and another cell is used for a dash or the word Yes when a task is completed (5 purple). This code works fine in several sheets and has been working until recently in a sheet where I keep track of completed tasks. What happens when I walk through the code step by step, is that the code suddenly stops after ".Columns(2).Value = (Now)". I don't understand what happens here. Any suggestions? TIA, Rob ******************* Code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim myRng As Range, Number As Integer Application.EnableEvents = True Number = Sh.Index Select Case Number Case 1, 2 With Target If .Cells.Count 1 Then Exit Sub 'If (.Row = 4 And .Row <= 100 And .Column = 8 And _ .Column Mod 2 = 0 And .Column <= 15) Then If (.Row = 4 And .Row <= 250 And .Column = 7) Then Set myRng = Target.Offset(0, 0).Resize(1, 1) Select Case LCase(Target.Value) Case Is = "0 blue" myRng.Interior.ColorIndex = 5 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "1 orange" myRng.Interior.ColorIndex = 46 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "2 green" myRng.Interior.ColorIndex = 4 myRng.Font.ColorIndex = 0 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "3 yellow" myRng.Interior.ColorIndex = 6 myRng.Font.ColorIndex = 0 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "4 red" myRng.Interior.ColorIndex = 3 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "-" Case Is = "5 purple" myRng.Interior.ColorIndex = 39 myRng.Font.ColorIndex = 2 .Columns(2).Value = (Now) .Columns(3).Value = "Yes" Case Else 'Set myRng = Target.Offset(0, -1).Resize(1, 1) 'myRng.Interior.ColorIndex = xlNone Set myRng = Target.Offset(0, 0).Resize(1, 1) myRng.Interior.ColorIndex = xlNone .Columns(2).Value = "" .Columns(3).Value = "-" End Select End If End With Case Else End Select End Sub ******************* |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Tab Colours | Excel Worksheet Functions | |||
Changing colours by conditions | Excel Discussion (Misc queries) | |||
Changing chart colours using VBA | Excel Programming | |||
Changing Colours With Protection | Excel Discussion (Misc queries) | |||
Changing colours in chart | Charts and Charting in Excel |