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