Ghost662
07-25-2015, 10:38 PM
Hello,
I'm having two issues with "Private Sub Worksheet_Change(ByVal Target As Range)":
It asks me to select a Macro, but I'm already telling it which Macro to run. The change event is in the appropriate sheet and the Macro is in Module 1.
There's a For Loop (For Each) in the Macro that's in Module 1 that affects multiple cells (i.e. targets) and, when I run the change event (and manually select the Macro from my first problem), it will only run for the current cell and then loop back to the change event.. instead of to the beginning of the For Loop.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Q9:T50")) Is Nothing Then
Availability
End If
End Sub
Below is the Macro. This is an old version with a few issues, but the loop setup and enableevents setup is the same in the current version. I suspect the issue is with the actual loop or the enableevents settings, but please let me know if it's not and I'll send the current code.
Sub Availability()
Application.EnableEvents = True
Application.ScreenUpdating = False
Dim totalRange As Range, totalCell As Range
Dim kdRange As Range, kdCell As Range
Dim jpRange As Range, jpCell As Range
Dim tcRange As Range, tcCell As Range
Dim assRange As Range, assCell As Range
FinalRow = Cells(Rows.Count, 16).End(xlUp).Row
Set totalRange = Sheet1.Range("Q9:T" & FinalRow).SpecialCells(xlCellTypeVisible)
Set kdRange = Sheet1.Range("Q9:Q" & FinalRow).SpecialCells(xlCellTypeVisible)
Set jpRange = Sheet1.Range("R9:R" & FinalRow).SpecialCells(xlCellTypeVisible)
Set tcRange = Sheet1.Range("S9:S" & FinalRow).SpecialCells(xlCellTypeVisible)
Set assRange = Sheet1.Range("T9:T" & FinalRow).SpecialCells(xlCellTypeVisible)
For Each kdCell In kdRange
If kdCell.Offset(0, 1) = "Yes" Or kdCell.Offset(0, 2) = "Yes" Then
kdCell = "N/A"
ElseIf kdCell.Offset(0, 1) = "No" And kdCell.Offset(0, 2) = "No" Then
kdCell = "Yes"
kdCell.Offset(0, 3) = "Davidson, Ken"
MsgBox "You're our last hope!"
Else
MsgBox "Finish coding"
End If
Next kdCell
Application.ScreenUpdating = False
Application.EnableEvents = False
End Sub
Thank you in advance for your help!
I'm having two issues with "Private Sub Worksheet_Change(ByVal Target As Range)":
It asks me to select a Macro, but I'm already telling it which Macro to run. The change event is in the appropriate sheet and the Macro is in Module 1.
There's a For Loop (For Each) in the Macro that's in Module 1 that affects multiple cells (i.e. targets) and, when I run the change event (and manually select the Macro from my first problem), it will only run for the current cell and then loop back to the change event.. instead of to the beginning of the For Loop.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Q9:T50")) Is Nothing Then
Availability
End If
End Sub
Below is the Macro. This is an old version with a few issues, but the loop setup and enableevents setup is the same in the current version. I suspect the issue is with the actual loop or the enableevents settings, but please let me know if it's not and I'll send the current code.
Sub Availability()
Application.EnableEvents = True
Application.ScreenUpdating = False
Dim totalRange As Range, totalCell As Range
Dim kdRange As Range, kdCell As Range
Dim jpRange As Range, jpCell As Range
Dim tcRange As Range, tcCell As Range
Dim assRange As Range, assCell As Range
FinalRow = Cells(Rows.Count, 16).End(xlUp).Row
Set totalRange = Sheet1.Range("Q9:T" & FinalRow).SpecialCells(xlCellTypeVisible)
Set kdRange = Sheet1.Range("Q9:Q" & FinalRow).SpecialCells(xlCellTypeVisible)
Set jpRange = Sheet1.Range("R9:R" & FinalRow).SpecialCells(xlCellTypeVisible)
Set tcRange = Sheet1.Range("S9:S" & FinalRow).SpecialCells(xlCellTypeVisible)
Set assRange = Sheet1.Range("T9:T" & FinalRow).SpecialCells(xlCellTypeVisible)
For Each kdCell In kdRange
If kdCell.Offset(0, 1) = "Yes" Or kdCell.Offset(0, 2) = "Yes" Then
kdCell = "N/A"
ElseIf kdCell.Offset(0, 1) = "No" And kdCell.Offset(0, 2) = "No" Then
kdCell = "Yes"
kdCell.Offset(0, 3) = "Davidson, Ken"
MsgBox "You're our last hope!"
Else
MsgBox "Finish coding"
End If
Next kdCell
Application.ScreenUpdating = False
Application.EnableEvents = False
End Sub
Thank you in advance for your help!