Bill_Biggs
Well-known Member
- Joined
- Feb 6, 2007
- Messages
- 1,216
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
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