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

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" <(E-Mail Removed)> wrote in message
news:999D6A20-0EA8-45C9-B8DD-(E-Mail Removed)...
> 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
>

You are not getting to the last of the nested "IFs".

Sub asdf()
If ActiveCell >= 0 And ActiveCell <= 4.99 Then
ActiveCell.Interior.ColorIndex = 4
End If
End Sub

will green an empty cell because the Value of an empty cell IS zero!
--
Gary''s Student - gsnu200771


"davethewelder" wrote:

> 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
>

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" <(E-Mail Removed)> wrote in message
> news:999D6A20-0EA8-45C9-B8DD-(E-Mail Removed)...
> > 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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:BDA77268-18E9-43D4-8468-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message
>> news:999D6A20-0EA8-45C9-B8DD-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>

Sub Formatcells_2()
Dim clrIdx As Long
Dim rng As Range, cell As Range
Set rng = ActiveSheet.Range("B2:B20")
For Each cell In rng
clrIdx = xlNone
If IsNumeric(cell.Value) Then
Select Case cell.Value
Case "": clrIdx = xlNone
Case Is >= 300: clrIdx = 3
Case Is >= 35: clrIdx = 45
Case Is >= 5: clrIdx = 6
Case Is >= 0: clrIdx = 4
'Case Is < 0: clrindx = 5 '?
Case Else: clrIdx = 15
End Select
End If
cell.Interior.ColorIndex = clrIdx
Next
End Sub

Written very quickly based on your posted example, so double check all
possible conditions

Regards,
Peter T

"davethewelder" <(E-Mail Removed)> wrote in message
news:BDA77268-18E9-43D4-8468-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
message
> > news:999D6A20-0EA8-45C9-B8DD-(E-Mail Removed)...
> > > 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
> > >
> >
> >
> >

If IsEmpty(ActiveCell) Then ActiveCell.Interior.ColorIndex = xlNone

I would recommend two changes..

1. Do not use ActiveCell, just refer to cell
2. Use a Select Case statement, must easier than nested ifs to both set up
and read.

e.g.........

Sub Formatcells1()
Dim MyCell As Range, myCi As Integer
For Each MyCell In ActiveSheet.Range("B2:B20").Cells
myCi = 2
If Not IsEmpty(MyCell) Then
Select Case MyCell
Case 0 To 4.99: myCi = 4
Case 5 To 35: myCi = 6
Case 35 To 299.99: myCi = 45
Case Is >= 300: myCi = 3
End Select
End If
MyCell.Interior.ColorIndex = myCi
Next MyCell
End Sub


--

Regards,
Nigel
(E-Mail Removed)



"davethewelder" <(E-Mail Removed)> wrote in message
news:BDA77268-18E9-43D4-8468-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message
>> news:999D6A20-0EA8-45C9-B8DD-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>

Gary, I did not know an empty cell has a value of zero. I understand now why
it was green. I have used the case statement and it works perfect.

Thanks to all for taking the time.

Davie

"Gary''s Student" wrote:

> You are not getting to the last of the nested "IFs".
>
> Sub asdf()
> If ActiveCell >= 0 And ActiveCell <= 4.99 Then
> ActiveCell.Interior.ColorIndex = 4
> End If
> End Sub
>
> will green an empty cell because the Value of an empty cell IS zero!
> --
> Gary''s Student - gsnu200771
>
>
> "davethewelder" wrote:
>
> > 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
> >