conditionally formatting

[Art]

This question is similar to one posted by ian on 2/27/10 about conditionally
formatting cells based on the values in other cells.

I understand the theory behind conditionally formatting and used it to make
alternative rows in my spreadsheet different colors.

I am making a course development spreadsheet in Excel 2007. Column H is used
to indicated when a course developer has completed the first part of a
course, AH is used to indicate they complete part 5 of 10, and AW is used for
completing part 10 of 10.

I get that i can conditionally format the blank cell in Column B based on
the contents of, say, Column H. I am just not sure

1. How to use the Conditionally Formatting tool to apply the "check" to
determine what color to fill the cell in Column B. (If Column H is blank,
shade gray. If Column H is not blank, shade red. If Column AH is not blank,
shade yellow. If Column AW is nto blank, shade green. I want to use Column B
to quickly scan the development status of each course, rather than having to
scroll far to the right.)

I know I have to likely create three rules, one for each color. I am just
not sure how to write the formula to say "If W is blank, change fill color of
Column B to gray."

2. How do I make sure that the formatting applies to all of the cells in
Column B, even if I insert new rows?

Every example I find on the Internet speaks to one cell (e.g., B4) being
conditionally formatted if, say W4 is blank or not. But, surely I don't have
to type a separate formula in the Conditionally Formatting pop-up for every
cell (e.g., B5, B6, B7, etc.), right?

3. If I already have alternating rows either blue or light blue (to make the
spreadsheet more readable), how do I avoid a conflict with rules? That is, if
a rule already turns a row blue, how can I still re-format that cell in that
blue row, say, RED, if Column W is blank, thus following the rule that the
cell in Column B is to be red.


Thanks so much for your help! All of you who respond to all of these posts
are amazing. I have learned so much about Excel through experimentation. It's
awesome there is a board like this where people respond...and respond quickly!


Art

 

[CellShocked]

I get that i can conditionally format the blank cell in Column B based on
the contents of, say, Column H. I am just not sure

Go to the MS user submitted templates page, and then "calendars", and
then, multi-year calendars, and then DL the one that says "with holidays"

That calendar has an area on it where it uses the value of one cell to
make an entire area appear or disappear. Cell coloring and formatting
goes blank unless there is data there and any data that does end up there
gets formatted as settings dictate.

It may be able to get you started as he uses colors and other
formatting that is cell content dependent.

 

[CellShocked]

1. How to use the Conditionally Formatting tool to apply the "check" to
determine what color to fill the cell in Column B. (If Column H is blank,
shade gray. If Column H is not blank, shade red.

You can add formatting for one cell content, and then add another rule
for the blank cell. Should work fine. I use a "Y" and an "N" and the Y
is green and the N is red. Works fine.

 

[Fred Smith]

Let's say data starts in row 2.

1. Highlight B2, and do the following:

Conditional Formatting
New Rule...
Use a Formula to determine which cells to format
In the box Format values where this formula is true, enter: =H2=""
Click Format...
Click Fill
Choose your grey color, OK, OK
Repeat for the next condition (=H2<>"")
Repeat for the next condition (=AH="")
Repeat for the next condition (=AH<>"")

2. Conditional formats, as the name implies, are formats. Therefore, you can
copy them the same way you do any formatting. The formulas will adjust the
same as regular formulas. One quick way to copy formats is to right-drag the
fill handle. When you release the mouse button, choose Fill Formatting Only.

3. The first condition that evaluates to true will be chosen. In the above
example, either H2 is blank or it's not. One of these will be true, and the
remaining ones won't be evaluated. Therefore you will need to amend your
conditions to use AND to get them all evaluated, such as:
=and(h2="",ah2="")
=and(h2="",ah2<>"")
=and(h2<>"",ah2="")
=and(h2<>"",ah2<>"")

Regards,
Fred