Fill sheet based on Values in Range

[jgj1988]

Im trying to use VBA to fill the entire sheet either red or green based on the cell values in column I. I made the code below but it is giving me an error with my range line.


I want to fill the sheet red if any cell in column I is equal to 1 or greater, and fill it green if any cell is equal to 0.

[pre]

Sub Macro1()

Sheets("Menu").Activate

With Menu
For Each Cell In .Range("I:I")
If Cell.Value >= 1 Then
Sheet.Interior.Color = vbRed
ElseIf Cell.Value = 0 Then
Sheet.Interior.Color = vbGreen
Else
Cell.Interior.Color = vbYellow
End If
Next Cell
End With

End Sub

[/pre]
Thanks for your help,


James

 

[Luke M]

I've got a couple of questions, but this shows you the syntax you need:

[pre]

Sub Macro1()
Dim c As Range
'I prefer c rather than cell, keeps it clear
'that it's just a variable, not a Cell

With Sheets("Menu")
'For Each c In .Range("I:I")
'Why are we checking the whole column?? This will
'take a very lone time, and it looks like you only want a single color
'So, lets do a quick CountIf instead
If WorksheetFunction.CountIf(.Range("I:I"), ">=1") Then
.Cells.Interior.Color = vbRed
ElseIf WorksheetFunction.CountIf(.Range("I:I"), 0) Then
.Cells.Interior.Color = vbGreen
Else
.Cells.Interior.Color = vbYellow
End If
'next c
End With

End Sub

[/pre]