Selection Change Question

[Bill_Biggs]

I have the following macro working on a sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("D36:D75")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Call Macro2
Else
Target = vbNullString

End If
End If
End Sub

If I click on a cell in the D column between D36 and D75, a check mark appears (a small caps "a" in Marlett font) in the cell I clicked on.

As soon as the check is created, I wanted another macro to be called that would select the third cell to the left of this one and change the contents to "N/A", and then the second one, and then the first one. This is it below:

Sub Macro2()

ActiveCell.Offset(0, -3).Select
ActiveCell.Value = "N/A"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "N/A"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "N/A"
ActiveCell.Offset(0, 1).Select
End Sub


But as soon as Macro2 selects another cell on the same page, the first macro above is reactivated, interrupting the new macro. The first macro steps through to the point that it discovers that the cell selected is not one it is supposed ot change, and the it goes to End If and stops, not completing the newer macro. Is there a way to totally turn off this macro until I select another cell in the D column?

Thanks,
Bill Biggs

 

[Scott Huish]

No need to do all that selecting. Ranges can be worked directly with VBA.
Those Offsets don't match your description however. Also why do you need a second macro that can be accomplished in one line, if you really want the three cells to the left to say N/A.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("D36:D75")) Is Nothing Then Exit Sub
Target.Font.Name = "Marlett"
If Target = vbNullString Then
    Target = "a"
    Target.Offset(, -3).Resize(, 3) = "N/A"
Else
    Target = vbNullString
End If
End Sub

 

[Bill_Biggs]

I thank you very much. I started down the path of trying to get away from the first macro and I guess I thought activating a second macro would do the trick. I am real tired today. But I am also still learning VBA. Sometimes I have flashes of brilliance, and then there are days like today. Again. I have already plugged it in and it works just fine. Thank you.