| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ElseIf
Hi, i am trying to write a condition that says if one of three cells in three
seperate ranges is blank, then fill the cell in another range with no colour. The code is identical to conditions above that highlight green etc. although those conditions are based on the value of the other cells and not their interior colour. No errors are returned but it simply misses the statement and continues to the else statement. I hope that makes sense! Any ideas? code... ElseIf rg2(i).Interior.ColorIndex = 0 Or rg3(i).Interior.ColorIndex = 0 Or rg4(i).Interior.ColorIndex = 0 Then rg(i).Interior.ColorIndex = 0 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ElseIf
hi,
the line "rg2(i).Interior.ColorIndex = 0" is asking if the interior color is 0. problem. there is no such color as 0. xl default color codes start at 1 which is black. by "blank" do you mean empty cell as in nothing in it. if so then use if isempty(rg2) or isempty(rg3) ect to fill with no color, use rg(i).Interior.ColorIndex = xlnone i'm not sure what the (i) is. Regards Frank -----Original Message----- Hi, i am trying to write a condition that says if one of three cells in three seperate ranges is blank, then fill the cell in another range with no colour. The code is identical to conditions above that highlight green etc. although those conditions are based on the value of the other cells and not their interior colour. No errors are returned but it simply misses the statement and continues to the else statement. I hope that makes sense! Any ideas? code... ElseIf rg2(i).Interior.ColorIndex = 0 Or rg3 (i).Interior.ColorIndex = 0 Or rg4(i).Interior.ColorIndex = 0 Then rg(i).Interior.ColorIndex = 0 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ElseIf
Would help to see the rest of your IF statement, but it is probably from the
ambiguity of the ORs you combine together. As an illustration: I am sure you mean (a=b) or (c=d) or (e=f), but I could also read that as a=(b or (c=d)) or (e=f): so the order in which you do the =s and ors makes a big difference. It is my practice to always use parentheses to force the order of logical tests the way I intend it - this is often "unnecessary" but I think it is good programming practice and makes for more readable code. So try this: ElseIf ((rg2(i).Interior.ColorIndex = 0) Or (rg3(i).Interior.ColorIndex = 0) Or (rg4(i).Interior.ColorIndex = 0)) Then... K Dales "tom1646" wrote: Hi, i am trying to write a condition that says if one of three cells in three seperate ranges is blank, then fill the cell in another range with no colour. The code is identical to conditions above that highlight green etc. although those conditions are based on the value of the other cells and not their interior colour. No errors are returned but it simply misses the statement and continues to the else statement. I hope that makes sense! Any ideas? code... ElseIf rg2(i).Interior.ColorIndex = 0 Or rg3(i).Interior.ColorIndex = 0 Or rg4(i).Interior.ColorIndex = 0 Then rg(i).Interior.ColorIndex = 0 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ElseIf
Thanks for your help. I have included all of the if statement, i warn you
now it is messy! I understood colorindex = 0 to mean 'no color' I have used this before and it seems to have worked fine. Thanks...... Set rg = Range("c3:z3") For i = 1 To rg.Cells.Count If rg2(i).Interior.color = RGB(0, 255, 0) And rg3(i).Interior.color = RGB(0, 255, 0) And rg4(i).Interior.color = RGB(0, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(255, 255, 0) And rg3(i).Interior.color = RGB(0, 255, 0) And rg4(i).Interior.color = RGB(0, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(0, 255, 0) And rg3(i).Interior.color = RGB(255, 255, 0) And rg4(i).Interior.color = RGB(0, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(0, 255, 0) And rg3(i).Interior.color = RGB(0, 255, 0) And rg4(i).Interior.color = RGB(255, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(255, 0, 0) And rg3(i).Interior.color = RGB(255, 0, 0) And rg4(i).Interior.color = RGB(255, 0, 0) Then rg(i).Interior.color = RGB(255, 0, 0) ElseIf rg2(i).Interior.color = RGB(255, 0, 0) And rg3(i).Interior.color = RGB(255, 0, 0) And rg4(i).Interior.color = RGB(255, 255, 0) Then rg(i).Interior.color = RGB(255, 0, 0) ElseIf rg2(i).Interior.color = RGB(255, 0, 0) And rg3(i).Interior.color = RGB(255, 255, 0) And rg4(i).Interior.color = RGB(255, 0, 0) Then rg(ie).Interior.color = RGB(255, 0, 0) ElseIf rg2(i).Interior.color = RGB(255, 255, 0) And rg3(i).Interior.color = RGB(255, 0, 0) And rg4(i).Interior.color = RGB(255, 0, 0) Then rg(i).Interior.color = RGB(255, 0, 0) ElseIf ((rg2(i).Interior.ColorIndex = 0) Or (rg3(i).Interior.ColorIndex = 0) Or (rg4(i).Interior.ColorIndex = 0)) Then rg(i).Interior.ColorIndex = "0" Else rg(i).Interior.color = RGB(255, 255, 0) End If Next i "K Dales" wrote: Would help to see the rest of your IF statement, but it is probably from the ambiguity of the ORs you combine together. As an illustration: I am sure you mean (a=b) or (c=d) or (e=f), but I could also read that as a=(b or (c=d)) or (e=f): so the order in which you do the =s and ors makes a big difference. It is my practice to always use parentheses to force the order of logical tests the way I intend it - this is often "unnecessary" but I think it is good programming practice and makes for more readable code. So try this: ElseIf ((rg2(i).Interior.ColorIndex = 0) Or (rg3(i).Interior.ColorIndex = 0) Or (rg4(i).Interior.ColorIndex = 0)) Then... K Dales |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ElseIf
sorted it now, thanks frank it was that i needed to use xlnone rather than 0
in the if statement. "tom1646" wrote: Thanks for your help. I have included all of the if statement, i warn you now it is messy! I understood colorindex = 0 to mean 'no color' I have used this before and it seems to have worked fine. Thanks...... Set rg = Range("c3:z3") For i = 1 To rg.Cells.Count If rg2(i).Interior.color = RGB(0, 255, 0) And rg3(i).Interior.color = RGB(0, 255, 0) And rg4(i).Interior.color = RGB(0, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(255, 255, 0) And rg3(i).Interior.color = RGB(0, 255, 0) And rg4(i).Interior.color = RGB(0, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(0, 255, 0) And rg3(i).Interior.color = RGB(255, 255, 0) And rg4(i).Interior.color = RGB(0, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(0, 255, 0) And rg3(i).Interior.color = RGB(0, 255, 0) And rg4(i).Interior.color = RGB(255, 255, 0) Then rg(i).Interior.color = RGB(0, 255, 0) ElseIf rg2(i).Interior.color = RGB(255, 0, 0) And rg3(i).Interior.color = RGB(255, 0, 0) And rg4(i).Interior.color = RGB(255, 0, 0) Then rg(i).Interior.color = RGB(255, 0, 0) ElseIf rg2(i).Interior.color = RGB(255, 0, 0) And rg3(i).Interior.color = RGB(255, 0, 0) And rg4(i).Interior.color = RGB(255, 255, 0) Then rg(i).Interior.color = RGB(255, 0, 0) ElseIf rg2(i).Interior.color = RGB(255, 0, 0) And rg3(i).Interior.color = RGB(255, 255, 0) And rg4(i).Interior.color = RGB(255, 0, 0) Then rg(ie).Interior.color = RGB(255, 0, 0) ElseIf rg2(i).Interior.color = RGB(255, 255, 0) And rg3(i).Interior.color = RGB(255, 0, 0) And rg4(i).Interior.color = RGB(255, 0, 0) Then rg(i).Interior.color = RGB(255, 0, 0) ElseIf ((rg2(i).Interior.ColorIndex = 0) Or (rg3(i).Interior.ColorIndex = 0) Or (rg4(i).Interior.ColorIndex = 0)) Then rg(i).Interior.ColorIndex = "0" Else rg(i).Interior.color = RGB(255, 255, 0) End If Next i "K Dales" wrote: Would help to see the rest of your IF statement, but it is probably from the ambiguity of the ORs you combine together. As an illustration: I am sure you mean (a=b) or (c=d) or (e=f), but I could also read that as a=(b or (c=d)) or (e=f): so the order in which you do the =s and ors makes a big difference. It is my practice to always use parentheses to force the order of logical tests the way I intend it - this is often "unnecessary" but I think it is good programming practice and makes for more readable code. So try this: ElseIf ((rg2(i).Interior.ColorIndex = 0) Or (rg3(i).Interior.ColorIndex = 0) Or (rg4(i).Interior.ColorIndex = 0)) Then... K Dales |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Still having problems with If --Elseif | Excel Discussion (Misc queries) | |||
| Elseif? | Excel Worksheet Functions | |||
| if elseif | Excel Discussion (Misc queries) | |||
| elseif formula | Excel Discussion (Misc queries) | |||
| If...Elseif...End If | Excel Programming | |||
Linear Mode
