ExcelBanter - ElseIf

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - ElseIf (https://www.excelbanter.com/excel-programming/313970-elseif.html)

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

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
.

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

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

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