| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colorindex 2 not formatting
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colorindex 2 not formatting
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colorindex 2 not formatting
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colorindex 2 not formatting
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colorindex 2 not formatting
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colorindex 2 not formatting
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" 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Colorindex 2 not formatting
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting to change ColorIndex? | Excel Programming | |||
| VBA ColorIndex Formatting | Excel Discussion (Misc queries) | |||
| interior.colorindex used with conditional formatting | Excel Worksheet Functions | |||
| conditional formatting and interior.colorindex | Excel Programming | |||
| ColorIndex | Excel Programming | |||
Linear Mode
