Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

IF; not for a cell, for a table

Status
Not open for further replies.

mohtogh

Chemical
Joined
Feb 21, 2001
Messages
144
Location
CA
Hi Guys,
If I want to check a cell is negative or positive I write:
=IF(A1>0,"OK","ERROR")
But I want to check a table,if there is even one negative number, I see ERROR.
Is that possible?

Thanks
 
You could try conditional formatting. For example, you can set the format for each cell in the table so the text turns red if the cell is less than zero. This would help to easily identify negative numbers in a table. Its not exactly what you were looking for, but it might help.

Conditional formatting is under the format menu.
 
To catch the error as it occurs:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Select Case Target.Row
Case 2 To 6
If Target.Value < 0 Then
MsgBox "Must be non-negative"
Target.Select
End If
End Select
End If
End Sub
This is set for Column 4 (D) and rows 2 to 6

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:
 
If you don't need to know where the negative number is, why not simply:

IF(MIN(table_range)>0,"OK","ERROR")
 
drat, I was hoping nobody had posted that yet!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top