Solved: if value less then 5 show warning in worksheet change

[mperrah]

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...
[VBA]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") ' 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[/VBA]

This is the sub that works
[VBA] 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[/VBA]

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal target As Range)
Const TCOL As String = "D"
Dim CheckCells As Range
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long

With Worksheets("Parts")

iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row

Set CheckCells = Worksheets("Parts").Range("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
Cells(i, TCOL).Offset(, 1).Value = ""
End Select
Next i

End If
End With
End Sub
[/vba]

[mperrah]

Man, you got it goin' on !
Thank you
That did it.

Mark

[mperrah]

One more thing,
How can I set this so column "E" is the min quantity
have "D" compare to "E"
Maybe set a variable each loop through the row (minVar)
in the case select use:
case select
Select Case Cells(i, TCOL).Value
Case minVar
Cells(i, TCOL).Offset(, 1).Value = "Inventory Low"

or with out changing the variable
Select Case Cells(i, TCOL).Value
Case Cells(i, TCOL).Value =< Cells(i, TCOL).offset(i, 1)
Cells(i, TCOL).Offset(, 2).Value = "Inventory Low"

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal target As Range)
Const TCOL As String = "D"
Dim CheckCells As Range
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long

With Me

iLastRow = .Cells(.Rows.Count, TCOL).End(xlUp).Row

Set CheckCells = Me.Range("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
If Cells(i, TCOL).Value < Cells(i, TCOL).Offset(, 1).Value Then
Cells(i, TCOL).Offset(, 2).Value = "Inventory Low"
Else
Cells(i, TCOL).Offset(, 2).Value = ""
End If
Next i

End If
End With
End Sub
[/vba]

[mperrah]

[VBA]
Sub youRock()
dim xld as kingofXL
dim i as integer

set xld = theman_themyth_thelegend

with allQuestionsUnderTheSun
for i to xld
if problem not is nothing then
xld = theSolution
else if
msg "A solution does not exist"
end if
next i
end with
end sub
[/VBA]


(Thank you)