Checking Individual Cells In A Range To Be Less Than 0

Hi Mark,

Thank you for the forum question.

You are very close. The line End If must be inside the For Each loop.



Private Sub Worksheet_Change(ByVal Target As Range)


Dim cel As Range
For Each cel In Range("D23:N23")

If cel.Value < 0 Then
MsgBox "Not enough technicians"
Exit For


End If

Next

End Sub


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Mark

Your code should work with a couple of changes.
Put the End if inside the For loop
Also press enter after Then
(in VBA the true result of the IF statement has to be on the line below the condition)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range

For Each cel In Range("D23:N23")

If cel.Value < 0 Then
MsgBox "Not enough technicians"
Exit For
End If
Next
End Sub

The message "Not enough technicians" will display if a negative number is entered.
If you wanted to include a blank cell type instead

IF cel.value <0 or cell.value=""


Mark, you can also achieve this without any VBA using Data Validation.

Highlight the cells D23:N23
Select Data, Data Validation
Choose Settings, Decimal
In Data choose Greater than or equal to
Type 0 for the minimum value

Hope it helps

Regards
Doug
Best STL