How to fill a column with color in statement

[anamarie30]

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

 

[Bob Flanagan]

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

 

[Bob Flanagan]

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]

Thanks Bob. It work great!

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