Excel VBA - Test for Insert row to trigger Workbook_Change Event

[beacon5]

Hi everybody,

I'm using Excel 2003 and I'm trying to modify a spreadsheet for work to make it easier to use.

The spreadsheet is basically a project task list where the first row contains the headers and the subsequent rows list the tasks, then the headers are repeated again. This repetition occurs about 10 times.

I've written code to make the tasks, which are identified as Task 1, Task 2, Task 3, etc. (clever, I know) automatically update if a task number is changed using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Worksheets("To-do list").Activate

    If ActiveCell.Column <> 3 Then
        Exit Sub
    End If

    If ActiveCell.Interior.ColorIndex = 47 Then
        Exit Sub
    ElseIf ActiveCell.Offset(-1, 0).Interior.ColorIndex = 47 Then
        ActiveCell.Value = "Task 1"
       While ActiveCell.Offset(1, 0).Interior.ColorIndex <> 47
            ActiveCell.Offset(1, 0).Select
           ActiveCell.Value = "Task " & (Right(ActiveCell.Offset(rowoffset:=-1), 1) + 1)
        Wend
    Else
        ActiveCell.Value = "Task " & (Right(ActiveCell.Offset(rowoffset:=-1), 1) + 1)
        While ActiveCell.Offset(1, 0).Interior.ColorIndex <> 47
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Value = "Task " & (Right(ActiveCell.Offset(rowoffset:=-1), 1) + 1)
        Wend
    End If

End Sub

The code looks at the task for a project, tests to see if the it's a header or not, then assigns the task number accordingly by grabbing the task number from the preceding cell/row and concatenating it to the string and repeating only for the tasks under that header (since I'm not an expert in VBA by any stretch of the imagination, I was fairly proud of this code).

The only condition the code above doesn't handle is when a row is inserted. When the row is inserted, I would like the tasks to update just as the do when I change a task.

Anyone have any ideas?

Example of the setup
Status | Project Name | Due Date | Notes |
_______| Task 1 |__________|_______|
_______| Task 2 |__________|_______|
_______| Task 3 |__________|_______|

 

[SkipVought]

Hi,

Please post VBA questions in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[beacon5]

Oops...sorry. I didn't see the VBA forum.

Thanks for pointing me in the right direction. =)