Trouble with If Then Else statement

Here's the code:

Private Sub Worksheet_Change(ByVal IncurredTC100 As Range)
    If Application.ActiveCell = "Milestone" Then
        Application.ActiveCell.Offset(1, 0).Select
        Application.ActiveCell.Offset(0, 1).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=MilestoneDrop1"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = "Invalid Entry"
                .InputMessage = ""
                .ErrorMessage = _
                "You must select a year from the list. These numbers are generated from the data you entered on the Study Specifications sheet under ""d. Key Project Dates."" Push cancel and try again."
                .ShowInput = False
                .ShowError = True
            End With
            Application.ActiveCell.Value = 1
            Application.ActiveCell.Offset(1, 0).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=MilestoneDrop2"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = False
                .ShowError = False
            End With
            Application.ActiveCell.Value = 1
            Application.ActiveCell.Offset(-1, 0).Select
    Else
        Application.ActiveCell.Offset(1, 0).Select
        Application.ActiveCell.Offset(0, 1).Select
            Application.ActiveCell.Validation.Delete
            Application.ActiveCell.FormulaR1C1 = False
    End If
End Sub

In case you're wondering, the reason I've used two Offset commands is because of merged cells. It might make more sense if you saw the xls.

When it runs, the word "False" cascades diagonally down to the right for hundreds of cells, but the error is underneath the Else section. Everything under If/Then works fine.

The other strange thing is that if I type anything in any cell in the worksheet (even outside the range defined in the macro), the word "False" cascades from that cell diagonally down to the right.

Any suggestions?

Hello JChandler22,

This is a common mistake when using the Worksheet_Change event. At the beginning of your code use Application.EnableEvents = False
and at the end add Application.EnableEvents = True. This prevents the cascade event you are seeing. When you change a cell it triggers the Worksheet_ChangeEvent. This tell Excel to ignore events while the code is running.

Sincerely,
Leith Ross

Ah. That makes perfect sense.

Thanks for the clarification. I'll try it out - I'm sure it will work!

Many thanks.

Another question... if you take a look at the code from the original post, I think I have something else wrong. I'm not too familiar with VBA, so I'm sort of fumbling along...

I'm trying to write the macro so that it only fires when certain cells are changed. Those cells are within a range that I've named IncurredTC100. Could someone tell me how I can achieve this?

Thanks!

Hello JChandler22,

Here is how. Add this line where appropriate in your Worksheet_Change code...

If Intersect(Target, IncurredTC100) Is Nothing Then
  ' Exit the macro if selected cell is not part of the range
   Exit Sub
End If

Sincerely,
Leith Ross