Any solution in order to have union of format using Conditional Fo
 

Hello:

By following this forum, I have succesfully created laternating color in
rows of my spreadsheet by using this formula: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still wakness of this
formula, because I have 35 thousand rows, ant it bocomes slow now. How eever
for the small size I need this, however I have a problem I need to have in
the same cell that if the content of the A1 < B1 it will show the read font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

I appreciate any idea provided.

Thanks.

Frank

Any solution in order to have union of format using Conditional Fo
 

Add an additional condition for A1<B1, but make it condition 1.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Hello:

By following this forum, I have succesfully created laternating color in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still wakness of
this
formula, because I have 35 thousand rows, ant it bocomes slow now. How
eever
for the small size I need this, however I have a problem I need to have in
the same cell that if the content of the A1 < B1 it will show the read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

I appreciate any idea provided.

Thanks.

Frank

Any solution in order to have union of format using Conditional Fo
 

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web search) and
not pick up newsgroups at all (and ideally weed out the all web forums, unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating color in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still wakness of
this
formula, because I have 35 thousand rows, ant it bocomes slow now.
However
for the small size I need this, however I have a problem I need to have in
the same cell that if the content of the A1 < B1 it will show the read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Thanks David for your explanation how ever I found it the result is, even the
data of A1B1 wil have the same shade format with red font. I want it shaded
and unshaded continually, but for the data A1<B1 we need it shaded and red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web search) and
not pick up newsgroups at all (and ideally weed out the all web forums, unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating color in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still wakness of
this
formula, because I have 35 thousand rows, ant it bocomes slow now.
However
for the small size I need this, however I have a problem I need to have in
the same cell that if the content of the A1 < B1 it will show the read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for just 3, just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result is, even
the
data of A1B1 wil have the same shade format with red font. I want it
shaded
and unshaded continually, but for the data A1<B1 we need it shaded and red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web search) and
not pick up newsgroups at all (and ideally weed out the all web forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow now.
However
for the small size I need this, however I have a problem I need to
have in
the same cell that if the content of the A1 < B1 it will show the read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Bob,

Thankyou for your comment. The problem is red font is important to indicate
that it is an overbudget. That is what I see the weakness of the prior excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for just 3, just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result is, even
the
data of A1B1 wil have the same shade format with red font. I want it
shaded
and unshaded continually, but for the data A1<B1 we need it shaded and red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web search) and
not pick up newsgroups at all (and ideally weed out the all web forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow now.
However
for the small size I need this, however I have a problem I need to
have in
the same cell that if the content of the A1 < B1 it will show the read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

There is a union, but it would be within the formula (AND(odd row, A1B1),
AND(even row, A1B1)), but this is how it gives rise to 4 conditions, and
why I suggest just having red on its own. But by writing this response, I
realise there are only 3, with conditions of

=AND($A$1$B$1,MOD(ROW(),2)=1) - red font, shaded
=AND($A$1$B$1,MOD(ROW(),2)=0) - red font, not shaded
=MOD(ROW(),2)=1 - shaded

I think that does all you want

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Bob,

Thankyou for your comment. The problem is red font is important to
indicate
that it is an overbudget. That is what I see the weakness of the prior
excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for just 3,
just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result is,
even
the
data of A1B1 wil have the same shade format with red font. I want it
shaded
and unshaded continually, but for the data A1<B1 we need it shaded and
red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web search)
and
not pick up newsgroups at all (and ideally weed out the all web
forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating
color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still
wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow now.
However
for the small size I need this, however I have a problem I need to
have in
the same cell that if the content of the A1 < B1 it will show the
read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Bob,

The result is still wrong. Red font is already true to indicate overbudget,
but alternating shaded does not work for the row.

Thanks if there is still a way out, coz my excel is 2002 version

Frank

"Bob Phillips" wrote:

There is a union, but it would be within the formula (AND(odd row, A1B1),
AND(even row, A1B1)), but this is how it gives rise to 4 conditions, and
why I suggest just having red on its own. But by writing this response, I
realise there are only 3, with conditions of

=AND($A$1$B$1,MOD(ROW(),2)=1) - red font, shaded
=AND($A$1$B$1,MOD(ROW(),2)=0) - red font, not shaded
=MOD(ROW(),2)=1 - shaded

I think that does all you want

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Bob,

Thankyou for your comment. The problem is red font is important to
indicate
that it is an overbudget. That is what I see the weakness of the prior
excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for just 3,
just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result is,
even
the
data of A1B1 wil have the same shade format with red font. I want it
shaded
and unshaded continually, but for the data A1<B1 we need it shaded and
red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web search)
and
not pick up newsgroups at all (and ideally weed out the all web
forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating
color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still
wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow now.
However
for the small size I need this, however I have a problem I need to
have in
the same cell that if the content of the A1 < B1 it will show the
read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

It worked in my test book as far as I understand what you are saying.

I had

A B
1 1 2
2 1 2
3 2 1
4 2 1

and rows 1 and 3 were shaded, and rows 3 and 4 were red font.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Bob,

The result is still wrong. Red font is already true to indicate
overbudget,
but alternating shaded does not work for the row.

Thanks if there is still a way out, coz my excel is 2002 version

Frank

"Bob Phillips" wrote:

There is a union, but it would be within the formula (AND(odd row,
A1B1),
AND(even row, A1B1)), but this is how it gives rise to 4 conditions, and
why I suggest just having red on its own. But by writing this response, I
realise there are only 3, with conditions of

=AND($A$1$B$1,MOD(ROW(),2)=1) - red font, shaded
=AND($A$1$B$1,MOD(ROW(),2)=0) - red font, not shaded
=MOD(ROW(),2)=1 - shaded

I think that does all you want

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Bob,

Thankyou for your comment. The problem is red font is important to
indicate
that it is an overbudget. That is what I see the weakness of the prior
excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for just
3,
just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result is,
even
the
data of A1B1 wil have the same shade format with red font. I want
it
shaded
and unshaded continually, but for the data A1<B1 we need it shaded
and
red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web
forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web
search)
and
not pick up newsgroups at all (and ideally weed out the all web
forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating
color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still
wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow
now.
However
for the small size I need this, however I have a problem I need
to
have in
the same cell that if the content of the A1 < B1 it will show
the
read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Thanks Bob for your answer. What we want is this
Shaded and ushaded will go one after the other, while red font is for the
overbudget, say colum A is actual and column B is Budget. With the
illustration below what we want is :

1. row 1 shaded with black font, since actual is still lower than budget
2. Row 2 unshaded still black font coz actual is still lower than budget
3. Row 3 is shaded but with red font ( since red indicating warning)
4. Row for unshaded but with the red font too, ( since red indicating
warning)
So shaded and unshaded will go one after the other on the regular basis ( I
do not know how to say it in English) but maybe you understand what I mean

This is the pattern that will go on and on

Thanks

Frank





"Bob Phillips" wrote:

It worked in my test book as far as I understand what you are saying.

I had

A B
1 1 2
2 1 2
3 2 1
4 2 1

and rows 1 and 3 were shaded, and rows 3 and 4 were red font.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Bob,

The result is still wrong. Red font is already true to indicate
overbudget,
but alternating shaded does not work for the row.

Thanks if there is still a way out, coz my excel is 2002 version

Frank

"Bob Phillips" wrote:

There is a union, but it would be within the formula (AND(odd row,
A1B1),
AND(even row, A1B1)), but this is how it gives rise to 4 conditions, and
why I suggest just having red on its own. But by writing this response, I
realise there are only 3, with conditions of

=AND($A$1$B$1,MOD(ROW(),2)=1) - red font, shaded
=AND($A$1$B$1,MOD(ROW(),2)=0) - red font, not shaded
=MOD(ROW(),2)=1 - shaded

I think that does all you want

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Bob,

Thankyou for your comment. The problem is red font is important to
indicate
that it is an overbudget. That is what I see the weakness of the prior
excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for just
3,
just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result is,
even
the
data of A1B1 wil have the same shade format with red font. I want
it
shaded
and unshaded continually, but for the data A1<B1 we need it shaded
and
red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web
forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and only
newsgroups, and you *should* be able to use Google (web
search)
and
not pick up newsgroups at all (and ideally weed out the all web
forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition 1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created laternating
color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still
wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow
now.
However
for the small size I need this, however I have a problem I need
to
have in
the same cell that if the content of the A1 < B1 it will show
the
read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Frank,

That is exactly what I mean. I have posted an example workbook at
http://cjoint.com/?lbjSs1HYSd to show you.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Thanks Bob for your answer. What we want is this
Shaded and ushaded will go one after the other, while red font is for the
overbudget, say colum A is actual and column B is Budget. With the
illustration below what we want is :

1. row 1 shaded with black font, since actual is still lower than budget
2. Row 2 unshaded still black font coz actual is still lower than budget
3. Row 3 is shaded but with red font ( since red indicating warning)
4. Row for unshaded but with the red font too, ( since red indicating
warning)
So shaded and unshaded will go one after the other on the regular basis
( I
do not know how to say it in English) but maybe you understand what I mean

This is the pattern that will go on and on

Thanks

Frank





"Bob Phillips" wrote:

It worked in my test book as far as I understand what you are saying.

I had

A B
1 1 2
2 1 2
3 2 1
4 2 1

and rows 1 and 3 were shaded, and rows 3 and 4 were red font.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Bob,

The result is still wrong. Red font is already true to indicate
overbudget,
but alternating shaded does not work for the row.

Thanks if there is still a way out, coz my excel is 2002 version

Frank

"Bob Phillips" wrote:

There is a union, but it would be within the formula (AND(odd row,
A1B1),
AND(even row, A1B1)), but this is how it gives rise to 4 conditions,
and
why I suggest just having red on its own. But by writing this
response, I
realise there are only 3, with conditions of

=AND($A$1$B$1,MOD(ROW(),2)=1) - red font, shaded
=AND($A$1$B$1,MOD(ROW(),2)=0) - red font, not shaded
=MOD(ROW(),2)=1 - shaded

I think that does all you want

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Bob,

Thankyou for your comment. The problem is red font is important to
indicate
that it is an overbudget. That is what I see the weakness of the
prior
excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for
just
3,
just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result
is,
even
the
data of A1B1 wil have the same shade format with red font. I
want
it
shaded
and unshaded continually, but for the data A1<B1 we need it
shaded
and
red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for
Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web
forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and
only
newsgroups, and you *should* be able to use Google (web
search)
and
not pick up newsgroups at all (and ideally weed out the all web
forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition
1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created
laternating
color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still
wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow
now.
However
for the small size I need this, however I have a problem I
need
to
have in
the same cell that if the content of the A1 < B1 it will show
the
read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Thank you Bob, You are very good too me, my worksheet consist of many budget
columns, so it would be non uniform in line shading because in another column
say labor budget, could be not overbudget, so shaded and unshaded would be in
opposite, so I leave it only one conditional format which is red font to
indicate overbudget, all will be unshaded. It seems too complicated.

Thanks for all your help, it enriches my knowlegge.

Frank

"Bob Phillips" wrote:

Frank,

That is exactly what I mean. I have posted an example workbook at
http://cjoint.com/?lbjSs1HYSd to show you.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Thanks Bob for your answer. What we want is this
Shaded and ushaded will go one after the other, while red font is for the
overbudget, say colum A is actual and column B is Budget. With the
illustration below what we want is :

1. row 1 shaded with black font, since actual is still lower than budget
2. Row 2 unshaded still black font coz actual is still lower than budget
3. Row 3 is shaded but with red font ( since red indicating warning)
4. Row for unshaded but with the red font too, ( since red indicating
warning)
So shaded and unshaded will go one after the other on the regular basis
( I
do not know how to say it in English) but maybe you understand what I mean

This is the pattern that will go on and on

Thanks

Frank





"Bob Phillips" wrote:

It worked in my test book as far as I understand what you are saying.

I had

A B
1 1 2
2 1 2
3 2 1
4 2 1

and rows 1 and 3 were shaded, and rows 3 and 4 were red font.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Bob,

The result is still wrong. Red font is already true to indicate
overbudget,
but alternating shaded does not work for the row.

Thanks if there is still a way out, coz my excel is 2002 version

Frank

"Bob Phillips" wrote:

There is a union, but it would be within the formula (AND(odd row,
A1B1),
AND(even row, A1B1)), but this is how it gives rise to 4 conditions,
and
why I suggest just having red on its own. But by writing this
response, I
realise there are only 3, with conditions of

=AND($A$1$B$1,MOD(ROW(),2)=1) - red font, shaded
=AND($A$1$B$1,MOD(ROW(),2)=0) - red font, not shaded
=MOD(ROW(),2)=1 - shaded

I think that does all you want

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Bob,

Thankyou for your comment. The problem is red font is important to
indicate
that it is an overbudget. That is what I see the weakness of the
prior
excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for
just
3,
just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"Frank Situmorang" wrote in message
...
Thanks David for your explanation how ever I found it the result
is,
even
the
data of A1B1 wil have the same shade format with red font. I
want
it
shaded
and unshaded continually, but for the data A1<B1 we need it
shaded
and
red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for
Conditional
Formatting)

This is a newsgroup not a web forum. One of the big differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with web
forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and
only
newsgroups, and you *should* be able to use Google (web
search)
and
not pick up newsgroups at all (and ideally weed out the all web
forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it condition
1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created
laternating
color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is still
wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes slow
now.
However
for the small size I need this, however I have a problem I
need
to
have in
the same cell that if the content of the A1 < B1 it will show
the
read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???

Any solution in order to have union of format using Conditiona
 

Frank,

If it is simple one column shading, that is if column L is over then shade
just L then just remove the $ signs in the formulae I used.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Frank Situmorang" wrote in message
...
Thank you Bob, You are very good too me, my worksheet consist of many
budget
columns, so it would be non uniform in line shading because in another
column
say labor budget, could be not overbudget, so shaded and unshaded would be
in
opposite, so I leave it only one conditional format which is red font to
indicate overbudget, all will be unshaded. It seems too complicated.

Thanks for all your help, it enriches my knowlegge.

Frank

"Bob Phillips" wrote:

Frank,

That is exactly what I mean. I have posted an example workbook at
http://cjoint.com/?lbjSs1HYSd to show you.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Thanks Bob for your answer. What we want is this
Shaded and ushaded will go one after the other, while red font is for
the
overbudget, say colum A is actual and column B is Budget. With the
illustration below what we want is :

1. row 1 shaded with black font, since actual is still lower than
budget
2. Row 2 unshaded still black font coz actual is still lower than
budget
3. Row 3 is shaded but with red font ( since red indicating warning)
4. Row for unshaded but with the red font too, ( since red indicating
warning)
So shaded and unshaded will go one after the other on the regular basis
( I
do not know how to say it in English) but maybe you understand what I
mean

This is the pattern that will go on and on

Thanks

Frank





"Bob Phillips" wrote:

It worked in my test book as far as I understand what you are saying.

I had

A B
1 1 2
2 1 2
3 2 1
4 2 1

and rows 1 and 3 were shaded, and rows 3 and 4 were red font.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Frank Situmorang" wrote in message
...
Bob,

The result is still wrong. Red font is already true to indicate
overbudget,
but alternating shaded does not work for the row.

Thanks if there is still a way out, coz my excel is 2002 version

Frank

"Bob Phillips" wrote:

There is a union, but it would be within the formula (AND(odd row,
A1B1),
AND(even row, A1B1)), but this is how it gives rise to 4
conditions,
and
why I suggest just having red on its own. But by writing this
response, I
realise there are only 3, with conditions of

=AND($A$1$B$1,MOD(ROW(),2)=1) - red font, shaded
=AND($A$1$B$1,MOD(ROW(),2)=0) - red font, not shaded
=MOD(ROW(),2)=1 - shaded

I think that does all you want

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"Frank Situmorang" wrote in message
...
Bob,

Thankyou for your comment. The problem is red font is important
to
indicate
that it is an overbudget. That is what I see the weakness of the
prior
excell
2007 that there is no union of format

Frank


"Bob Phillips" wrote:

Frank,

That sounds like 4 conditions to handle all possibilities

shaded, red font
unshaded, red font
shaded
unshaded

which is not supported prior to Excel 2007. Why not just go for
just
3,
just
use red font or shaded/unshaded on row 1?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)

"Frank Situmorang" wrote in
message
...
Thanks David for your explanation how ever I found it the
result
is,
even
the
data of A1B1 wil have the same shade format with red font. I
want
it
shaded
and unshaded continually, but for the data A1<B1 we need it
shaded
and
red
font color, could you explain more?

Thanks,

Frank

Hi Frank,
Excel 2007 can combine Conditional Formatting, but
pre Excel 2007, I believe you would need three conditions
and I'm including this as was stated. For more information
on
Conditional Formatting see
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Condition 1: =AND(A1<B1,MOD(SUBTOTAL(3,$A$7:$A7),2)=0)
Condition 2: =A1<B1
Condition 3: =MOD(SUBTOTAL(3,$A$7:$A7),2)=0

If you need more than 3 conditions and don't have Excel 2007
see http://www.xldynamic.com/source/xld.....Download.html
(actually I have yet to look at Excel 2007 limits for
Conditional
Formatting)

This is a newsgroup not a web forum. One of the big
differences
is that web forums mess up web searches. Hardly anyone who
answers many questions here would have anything to do with
web
forums
or web access for newsgroups.

You should be able to search Google Groups for newsgroups and
only
newsgroups, and you *should* be able to use Google (web
search)
and
not pick up newsgroups at all (and ideally weed out the all
web
forums,
unless
searching a specific web forum).
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm


"Bob Phillips" wrote ...
Add an additional condition for A1<B1, but make it
condition
1.

"Frank Situmorang" wrote ...
By following this forum, I have succesfully created
laternating
color
in
rows of my spreadsheet by using this formula:
=MOD(SUBTOTAL(3,$A$7:$A7),2)=0

I need it to facilitate easy reading although there is
still
wakness
of
this
formula, because I have 35 thousand rows, ant it bocomes
slow
now.
However
for the small size I need this, however I have a problem I
need
to
have in
the same cell that if the content of the A1 < B1 it will
show
the
read
font
color. AND IT DOES NOT WORK, ANY SOLUTIONS TO THIS???