Banding with Conditional Formatting with Multiple Conditions

[Guest]

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?

 

[Guest]

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

 

[Bob Phillips]

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)

 

[JulieD]

Hi Geremia

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

--so all odd rows banded, bold & italicized when f69>D72
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

 

[Guest]

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]

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

 

[Guest]

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]

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