Thread
:
Colorindex 2 not formatting
View Single Post
#
7
Posted to microsoft.public.excel.programming
Nigel[_2_]
external usenet poster
Posts: 735
Colorindex 2 not formatting
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
"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
Reply With Quote
Nigel[_2_]
View Public Profile
Find all posts by Nigel[_2_]