Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 1st 05, 08:09 AM
Geremia Doan
 
Posts: n/a
Default Banding with Conditional Formatting with Multiple Conditions

I can set up banding using Conditional Formatting fust fine. The problem I
am having is that there are other conditions I need to have happen at the
same time. For example, when cell F69 is greater than cell D72, I need it to
be BOLD and ITALICIZED. When I use the banding formula in Conditional
Formatting as the first Condition, it overrides the other conditions, so the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding Condition.
Does anyone know if there is a way to get around this problem? A lengthy
formula, perhaps, to put into Conditional Formatting that will perform both
functions?

  #2   Report Post  
Old February 1st 05, 09:47 AM
Max
 
Posts: n/a
Default

Perhaps you could paste a sample of all the CF formulas (for Conditions 1 to
3) used currently for a particular cell ? Think it would make it much easier
for clarity, and for help to arrive

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Geremia Doan" wrote:

I can set up banding using Conditional Formatting fust fine. The problem I
am having is that there are other conditions I need to have happen at the
same time. For example, when cell F69 is greater than cell D72, I need it to
be BOLD and ITALICIZED. When I use the banding formula in Conditional
Formatting as the first Condition, it overrides the other conditions, so the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding Condition.
Does anyone know if there is a way to get around this problem? A lengthy
formula, perhaps, to put into Conditional Formatting that will perform both
functions?

  #3   Report Post  
Old February 1st 05, 10:40 AM
Bob Phillips
 
Posts: n/a
Default

If you need more than 3 conditions, check out
http://xldynamic.com/xld.CFPlus.Dowmload.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Max" wrote in message
...
Perhaps you could paste a sample of all the CF formulas (for Conditions 1

to
3) used currently for a particular cell ? Think it would make it much

easier
for clarity, and for help to arrive

--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----

"Geremia Doan" wrote:

I can set up banding using Conditional Formatting fust fine. The

problem I
am having is that there are other conditions I need to have happen at

the
same time. For example, when cell F69 is greater than cell D72, I need

it to
be BOLD and ITALICIZED. When I use the banding formula in Conditional
Formatting as the first Condition, it overrides the other conditions, so

the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding

Condition.
Does anyone know if there is a way to get around this problem? A

lengthy
formula, perhaps, to put into Conditional Formatting that will perform

both
functions?



  #4   Report Post  
Old February 1st 05, 03:44 PM
JulieD
 
Posts: n/a
Default

Hi Geremia

not sure what you want Bold & Italicized - all the "banded" rows or row #69.

--so all odd rows banded, bold & italicized when f69D72
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72)
-set formatting for banding colour & font bold & italicized

2nd condition
=MOD(ROW()-1,2)+1<=1

--only row 69 bold & italicized
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69)
-set formatting for banding colour & font bold & italicized

2nd condition
- as above

Hope this helps
Cheers
JulieD

"Geremia Doan" wrote:

I can set up banding using Conditional Formatting fust fine. The problem
I
am having is that there are other conditions I need to have happen at the
same time. For example, when cell F69 is greater than cell D72, I need
it to
be BOLD and ITALICIZED. When I use the banding formula in Conditional
Formatting as the first Condition, it overrides the other conditions, so
the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding
Condition.
Does anyone know if there is a way to get around this problem? A
lengthy
formula, perhaps, to put into Conditional Formatting that will perform
both
functions?



  #5   Report Post  
Old February 1st 05, 04:21 PM
Geremia Doan
 
Posts: n/a
Default

Let me clarify what I want. I have a column of 68 cells. Each cell has a
date in it. I have another cell below it that always has the date it was 6
months ago in it. Using CF, I made it so that whenever the date in one of
the 68 cells in the column is prior to 6 months ago, it becoms bold and
italicized.
I also want to set up banding so that it's easier to follow the rows when I
print out the spreadsheet.
I am having trouble accomplishing both of those things at the same time.
I guess I could put it this way: I want to shade all odd rows in a range.
In all odd and even rows of my particular column, I want a bold and
italicized font if the date in the cell is older than 6 months; and I want to
keep the shading in each odd row.
Does that make better sense?
Thank you for your help and patience.

"JulieD" wrote:

Hi Geremia

not sure what you want Bold & Italicized - all the "banded" rows or row #69.

--so all odd rows banded, bold & italicized when f69D72
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72)
-set formatting for banding colour & font bold & italicized

2nd condition
=MOD(ROW()-1,2)+1<=1

--only row 69 bold & italicized
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69)
-set formatting for banding colour & font bold & italicized

2nd condition
- as above

Hope this helps
Cheers
JulieD

"Geremia Doan" wrote:

I can set up banding using Conditional Formatting fust fine. The problem
I
am having is that there are other conditions I need to have happen at the
same time. For example, when cell F69 is greater than cell D72, I need
it to
be BOLD and ITALICIZED. When I use the banding formula in Conditional
Formatting as the first Condition, it overrides the other conditions, so
the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding
Condition.
Does anyone know if there is a way to get around this problem? A
lengthy
formula, perhaps, to put into Conditional Formatting that will perform
both
functions?






  #6   Report Post  
Old February 1st 05, 04:38 PM
JulieD
 
Posts: n/a
Default

Hi Geremia

what is the formula you're using in conditional formatting for
"Using CF, I made it so that whenever the date in one of the 68 cells in the
column is prior to 6 months ago, it becoms bold and
italicized."

as it's difficult to tie this in with your first example.

Cheers
JulieD


"Geremia Doan" wrote in message
...
Let me clarify what I want. I have a column of 68 cells. Each cell has a
date in it. I have another cell below it that always has the date it was
6
months ago in it. Using CF, I made it so that whenever the date in one of
the 68 cells in the column is prior to 6 months ago, it becoms bold and
italicized.
I also want to set up banding so that it's easier to follow the rows when
I
print out the spreadsheet.
I am having trouble accomplishing both of those things at the same time.
I guess I could put it this way: I want to shade all odd rows in a range.
In all odd and even rows of my particular column, I want a bold and
italicized font if the date in the cell is older than 6 months; and I want
to
keep the shading in each odd row.
Does that make better sense?
Thank you for your help and patience.

"JulieD" wrote:

Hi Geremia

not sure what you want Bold & Italicized - all the "banded" rows or row
#69.

--so all odd rows banded, bold & italicized when f69D72
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72)
-set formatting for banding colour & font bold & italicized

2nd condition
=MOD(ROW()-1,2)+1<=1

--only row 69 bold & italicized
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69)
-set formatting for banding colour & font bold & italicized

2nd condition
- as above

Hope this helps
Cheers
JulieD

"Geremia Doan" wrote:

I can set up banding using Conditional Formatting fust fine. The
problem
I
am having is that there are other conditions I need to have happen at
the
same time. For example, when cell F69 is greater than cell D72, I
need
it to
be BOLD and ITALICIZED. When I use the banding formula in Conditional
Formatting as the first Condition, it overrides the other conditions,
so
the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding
Condition.
Does anyone know if there is a way to get around this problem? A
lengthy
formula, perhaps, to put into Conditional Formatting that will
perform
both
functions?






  #7   Report Post  
Old February 1st 05, 10:49 PM
Geremia Doan
 
Posts: n/a
Default

JulieD,
I am not very good at explaining these things and I apologize for that.
When I set up multiple conditions in Conditional Formatting, conditions 1 and
2 override condition 3, when condition 1 contains the 'banding' formula and
conditions 2 and 3 contain a 'is greater' or 'is less' formula with a format
of BOLD or BOLD ITALICIZED. So what happens is every other row is shaded,
but where conditions 2 and 3 would normally apply, making the contents of the
cell BOLD or BOLD ITALICIZED, the cell is shaded and the contents of the cell
are neither BOLD or ITALICIZED. If I change Conditonal Formatting so that
the 'banding' formula is condition 3, conditions 1 and 2 override condition 3
making the contents of the cell BOLD or BOLD ITALICIZED, but the cell is not
shaded.
If that still doesn't make sense, maybe you could take a peek at the
spreadsheet. You can see it at home.comcast.net/~geremiadoan/ and its the
link labeled Territory.xls. It's the second item from the top.
My aim is to shade every other row, yet still be able to sort and keep this
shading, as well as other conditional formatting that I already have set up.
The copy that I have up on the website was saved prior to my having attempted
the shading of odd numbered rows, but after having set up conditional
formatting to change contents of cells to BOLD and BOLD ITALICIZED.

Thank you for your help. You're the best!

Geremia

"JulieD" wrote:

Hi Geremia

what is the formula you're using in conditional formatting for
"Using CF, I made it so that whenever the date in one of the 68 cells in the
column is prior to 6 months ago, it becoms bold and
italicized."

as it's difficult to tie this in with your first example.

Cheers
JulieD


"Geremia Doan" wrote in message
...
Let me clarify what I want. I have a column of 68 cells. Each cell has a
date in it. I have another cell below it that always has the date it was
6
months ago in it. Using CF, I made it so that whenever the date in one of
the 68 cells in the column is prior to 6 months ago, it becoms bold and
italicized.
I also want to set up banding so that it's easier to follow the rows when
I
print out the spreadsheet.
I am having trouble accomplishing both of those things at the same time.
I guess I could put it this way: I want to shade all odd rows in a range.
In all odd and even rows of my particular column, I want a bold and
italicized font if the date in the cell is older than 6 months; and I want
to
keep the shading in each odd row.
Does that make better sense?
Thank you for your help and patience.

"JulieD" wrote:

Hi Geremia

not sure what you want Bold & Italicized - all the "banded" rows or row
#69.

--so all odd rows banded, bold & italicized when f69D72
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72)
-set formatting for banding colour & font bold & italicized

2nd condition
=MOD(ROW()-1,2)+1<=1

--only row 69 bold & italicized
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69)
-set formatting for banding colour & font bold & italicized

2nd condition
- as above

Hope this helps
Cheers
JulieD

"Geremia Doan" wrote:

I can set up banding using Conditional Formatting fust fine. The
problem
I
am having is that there are other conditions I need to have happen at
the
same time. For example, when cell F69 is greater than cell D72, I
need
it to
be BOLD and ITALICIZED. When I use the banding formula in Conditional
Formatting as the first Condition, it overrides the other conditions,
so
the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding
Condition.
Does anyone know if there is a way to get around this problem? A
lengthy
formula, perhaps, to put into Conditional Formatting that will
perform
both
functions?






  #8   Report Post  
Old February 2nd 05, 04:14 PM
JulieD
 
Posts: n/a
Default

Hi Geremia

i've had a look at the workbook and the best i can do for you is:
apply banding to the odd rows AND if the date in column F is older than 6
months make all the text in that row go bold & italicized - i can't get JUST
column F for the bold & italicized.

if this is OK, select the rows that you want to apply the conditional
formatting to, ensure that row 2 is the first row on the screen and the two
conditions you need are"
Condition 1:
Formula is
=AND(MOD(ROW()-1,2)+1<=1,$F2<=$D$72-240)
set the format for bold & italicized and for the banding colour

Condition 2:
Formula is
=MOD(ROW()-1,2)+1<=1
set the format for the banding colour.

.... i've got a workbook i can send you if you get stuck.

Hope this helps
Cheers
JulieD



"Geremia Doan" wrote in message
...
JulieD,
I am not very good at explaining these things and I apologize for that.
When I set up multiple conditions in Conditional Formatting, conditions 1
and
2 override condition 3, when condition 1 contains the 'banding' formula
and
conditions 2 and 3 contain a 'is greater' or 'is less' formula with a
format
of BOLD or BOLD ITALICIZED. So what happens is every other row is shaded,
but where conditions 2 and 3 would normally apply, making the contents of
the
cell BOLD or BOLD ITALICIZED, the cell is shaded and the contents of the
cell
are neither BOLD or ITALICIZED. If I change Conditonal Formatting so that
the 'banding' formula is condition 3, conditions 1 and 2 override
condition 3
making the contents of the cell BOLD or BOLD ITALICIZED, but the cell is
not
shaded.
If that still doesn't make sense, maybe you could take a peek at the
spreadsheet. You can see it at home.comcast.net/~geremiadoan/ and its the
link labeled Territory.xls. It's the second item from the top.
My aim is to shade every other row, yet still be able to sort and keep
this
shading, as well as other conditional formatting that I already have set
up.
The copy that I have up on the website was saved prior to my having
attempted
the shading of odd numbered rows, but after having set up conditional
formatting to change contents of cells to BOLD and BOLD ITALICIZED.

Thank you for your help. You're the best!

Geremia

"JulieD" wrote:

Hi Geremia

what is the formula you're using in conditional formatting for
"Using CF, I made it so that whenever the date in one of the 68 cells in
the
column is prior to 6 months ago, it becoms bold and
italicized."

as it's difficult to tie this in with your first example.

Cheers
JulieD


"Geremia Doan" wrote in message
...
Let me clarify what I want. I have a column of 68 cells. Each cell
has a
date in it. I have another cell below it that always has the date it
was
6
months ago in it. Using CF, I made it so that whenever the date in one
of
the 68 cells in the column is prior to 6 months ago, it becoms bold and
italicized.
I also want to set up banding so that it's easier to follow the rows
when
I
print out the spreadsheet.
I am having trouble accomplishing both of those things at the same
time.
I guess I could put it this way: I want to shade all odd rows in a
range.
In all odd and even rows of my particular column, I want a bold and
italicized font if the date in the cell is older than 6 months; and I
want
to
keep the shading in each odd row.
Does that make better sense?
Thank you for your help and patience.

"JulieD" wrote:

Hi Geremia

not sure what you want Bold & Italicized - all the "banded" rows or
row
#69.

--so all odd rows banded, bold & italicized when f69D72
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72)
-set formatting for banding colour & font bold & italicized

2nd condition
=MOD(ROW()-1,2)+1<=1

--only row 69 bold & italicized
1st condition
=AND(MOD(ROW()-1,2)+1<=1,$F$69$D$72,ROW()=69)
-set formatting for banding colour & font bold & italicized

2nd condition
- as above

Hope this helps
Cheers
JulieD

"Geremia Doan" wrote:

I can set up banding using Conditional Formatting fust fine. The
problem
I
am having is that there are other conditions I need to have happen
at
the
same time. For example, when cell F69 is greater than cell D72, I
need
it to
be BOLD and ITALICIZED. When I use the banding formula in
Conditional
Formatting as the first Condition, it overrides the other
conditions,
so
the
cell is shaded, but not BOLD and ITALICIZED. If I make it the last
Condition, the BOLD and ITALICIZED condition overrides the banding
Condition.
Does anyone know if there is a way to get around this problem? A
lengthy
formula, perhaps, to put into Conditional Formatting that will
perform
both
functions?










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
More than 3 Conditional Formatting Conditions Beth H Excel Worksheet Functions 12 January 6th 06 08:35 PM
Copy conditional formatting across multiple rows? Gil Excel Discussion (Misc queries) 1 January 11th 05 12:27 PM
Conditional Formatting Blank =white.. but 4 conditions Junior Excel Worksheet Functions 3 December 18th 04 09:32 AM
Adding more than three Conditions to 'Conditional Formatting' David McRitchie Excel Discussion (Misc queries) 1 November 27th 04 07:03 PM
How do I use conditional formatting for multiple rows? Jim Johnson Excel Worksheet Functions 1 October 30th 04 03:36 AM


All times are GMT +1. The time now is 09:33 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017