Sub Formatcells1()
Dim MyCell As Range
For Each MyCell In ActiveSheet.Range("B2:B20").Cells

Select Case True

Case MyCell = ""
MyCell.Interior.ColorIndex = xlColorIndexNone

Case MyCell.Value = 0 And MyCell.Value <= 4.99
MyCell.Interior.ColorIndex = 4

Case MyCell = 5 And MyCell <= 35
MyCell.Interior.ColorIndex = 6

Case MyCell = 35 And MyCell <= 299.99
MyCell.Interior.ColorIndex = 45

Case MyCell = 300
MyCell.Interior.ColorIndex = 3

End Select
Next MyCell
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"davethewelder" wrote in message
...
Peter, I tried your suggestion both ways and unfortunately it is still
shading it green. I can see the logic. Tip about myCell taken onboard.

Davie

"Peter T" wrote:

Your first If is like this (simplified)

If ActiveCell = 0 then make it green and loop next.

Suggest make your first test
If len(MyCell.value) = 0 then ' or = MyCell = ""
mycell.interior.colorindex = xlNone ' or 2 if you prefer ?
etc

In passing, no need to activate cells. just your ref' MyCell

Regards,
Peter T


"davethewelder" wrote in
message
...
Hi, I am using an IF statement to format values in a table. Code
below.
Sub Formatcells1()
Dim MyCell As Range
For Each MyCell In ActiveSheet.Range("B2:B20").Cells
MyCell.Select

If ActiveCell = 0 And ActiveCell <= 4.99 Then
ActiveCell.Interior.ColorIndex = 4
Else
If ActiveCell = 5 And ActiveCell <= 35 Then
ActiveCell.Interior.ColorIndex = 6
Else
If ActiveCell = 35 And ActiveCell <= 299.99 Then
ActiveCell.Interior.ColorIndex = 45
Else
If ActiveCell = 300 Then
ActiveCell.Interior.ColorIndex = 3
Else
If ActiveCell = "" Then
ActiveCell.Interior.ColorIndex = 2
End If
End If
End If
End If
End If
Next MyCell
End Sub

My problem is that if a cell is blank it is being coloured green. I
have
tried the option "" but to no avail.
I am baffled as it does not seem logical.

Any help would be appreciated.

Thanks

Davie