Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditionally formatting
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditionally formatting
On Sat, 27 Feb 2010 17:15:01 -0800, Art
wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditionally formatting
On Sat, 27 Feb 2010 17:15:01 -0800, Art
wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditionally formatting
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 "Art" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditionally Formatting a Range | Excel Worksheet Functions | |||
conditionally formatting comments | Excel Discussion (Misc queries) | |||
Conditionally formatting currency | Excel Worksheet Functions | |||
Conditionally Formatting | Excel Worksheet Functions | |||
Conditionally formatting other cells? | Excel Worksheet Functions |