mperrah
09-16-2007, 09:12 PM
I have a sub that works with a click,
I'm trying to make it automatic with sheet value changes in the column "D"
This is the worksheet_change I'm debugging...
Private Sub Worksheet_Change(ByVal target As Range)
Const TCOL As String = "D"
Dim CheckCells
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long
With Worksheets("Parts")
iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row
CheckCells = Worksheets("Parts").Range("D:D") ' this should be d1 to last row
If target.Cells.Count > 1 Then Exit Sub
If Not Intersect(target, CheckCells) Is Nothing Then ' trouble here
For i = 1 To iLastRow
Select Case Cells(i, TCOL).Value
Case 1 To 5
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"
Case " "
Case Else
End Select
Next i
End If
End With
End Sub
This is the sub that works
Sub lowInventory()
Const TCOL As String = "D"
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long
With Worksheets("Parts")
iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row
For i = 1 To iLastRow
Select Case Cells(i, TCOL).Value
Case 1 To 5
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"
Case " "
Case Else
End Select
Next i
End With
End Sub
I'm trying to make it automatic with sheet value changes in the column "D"
This is the worksheet_change I'm debugging...
Private Sub Worksheet_Change(ByVal target As Range)
Const TCOL As String = "D"
Dim CheckCells
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long
With Worksheets("Parts")
iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row
CheckCells = Worksheets("Parts").Range("D:D") ' this should be d1 to last row
If target.Cells.Count > 1 Then Exit Sub
If Not Intersect(target, CheckCells) Is Nothing Then ' trouble here
For i = 1 To iLastRow
Select Case Cells(i, TCOL).Value
Case 1 To 5
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"
Case " "
Case Else
End Select
Next i
End If
End With
End Sub
This is the sub that works
Sub lowInventory()
Const TCOL As String = "D"
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long
With Worksheets("Parts")
iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row
For i = 1 To iLastRow
Select Case Cells(i, TCOL).Value
Case 1 To 5
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"
Case " "
Case Else
End Select
Next i
End With
End Sub