I want to make a code that check in a range of cells if the number entered by
the user is greater that 0, turn that cell in red and send a message to the
user. I tried this code but not work for me.

Dim rng As Range

Set rng = Range("$B$5:$B$11")

If rng 0 Then
MessageBox.Show ("You have an Alert!")
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If

End Sub

In the worksheet's code sheet (access by right clicking on the tab and
selecting view code), you can put code like the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyR As Range
Dim cell As Range
On Error Resume Next
Set anyR = Intersect(Target, Range("$B$5:$B$11"))
On Error GoTo 0
If anyR Is Nothing Then Exit Sub
For Each cell In anyR
If cell.Value 0 Then
MsgBox "Cell " & cell.Address & " value is 0)"
End If
Next
End Sub

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


"anamarie30" wrote in message
...
I want to make a code that check in a range of cells if the number entered
by
the user is greater that 0, turn that cell in red and send a message to
the
user. I tried this code but not work for me.

Dim rng As Range

Set rng = Range("$B$5:$B$11")

If rng 0 Then
MessageBox.Show ("You have an Alert!")
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If

End Sub

woops. Forgot to color the cell. The following does that :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyR As Range
Dim cell As Range
On Error Resume Next
Set anyR = Intersect(Target, Range("$B$5:$B$11"))
On Error GoTo 0
If anyR Is Nothing Then Exit Sub
For Each cell In anyR
If cell.Value 0 Then
MsgBox "Cell " & cell.Address & " value is 0)"
cell.Interior.ColorIndex = 3
End If
Next
End Sub

Bob

"anamarie30" wrote in message
...
I want to make a code that check in a range of cells if the number entered
by
the user is greater that 0, turn that cell in red and send a message to
the
user. I tried this code but not work for me.

Dim rng As Range

Set rng = Range("$B$5:$B$11")

If rng 0 Then
MessageBox.Show ("You have an Alert!")
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If

End Sub

Thanks Bob. It work great!

"Bob Flanagan" wrote:

woops. Forgot to color the cell. The following does that :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyR As Range
Dim cell As Range
On Error Resume Next
Set anyR = Intersect(Target, Range("$B$5:$B$11"))
On Error GoTo 0
If anyR Is Nothing Then Exit Sub
For Each cell In anyR
If cell.Value 0 Then
MsgBox "Cell " & cell.Address & " value is 0)"
cell.Interior.ColorIndex = 3
End If
Next
End Sub

Bob

"anamarie30" wrote in message
...
I want to make a code that check in a range of cells if the number entered
by
the user is greater that 0, turn that cell in red and send a message to
the
user. I tried this code but not work for me.

Dim rng As Range

Set rng = Range("$B$5:$B$11")

If rng 0 Then
MessageBox.Show ("You have an Alert!")
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If

End Sub