MS Excel 2007 Conditional Formatting based on DAY?
I have a spreadsheet that has all the days of the week in the first column. In the second column it has the corresponding dates to each of those days. In the third column, I need to highlight (fill with color) certain cells based on the day of the week provided in column 1. Ex. All Mon's and Wed's will be highlighted blue, Tue's and Thu's will be highlighted yellow, and Fri's, Sat's and Sun's will be highlighted green. Can you please help me figure out how to do this without having to do it manually?
I already asked this question and got a great reply from brayden: "In the column where you need the color, click the conditional formatting arrow (it's on the home tab of th eribbon). Select "highlighe cells rules" and select "equal to". Then in the box type Monday, and then in the box to the right, click the arrow and select custom format and select whatever color you want. Set up you a rule for each day's color that you need. Then you can copy the formatting to any cells on the spreadsheet after you establish your rules."
This helped a great deal, however, I need to be able to change the empty cells in column three in respect to the contents in column one. I still cannot figure out how to do that.
Also, on a side note, this is my first (second now) time asking a question on Yahoo Answers. Is there an easier way to reply to an answer, than making a new post? I swear I'm not retarded.
voyager2009-03-17T15:15:26Z
Favorite Answer
You have to use conditional formatting based on a formula since you have to reference the cells in another column..
For example, do this for Mondays & Wednesdays
- Click on "Conditional Format" - Select "New Rule" - Then select "Use a formula to determine which cells to format" - Type =OR(A2="Mon",A2="Wed") in the inputbox - Then select "Format" & pick the color you want - OK
Since condition is based on the cells in column 1, it is only when you change a value in column 1, the colors will change..
Email me if you need more help...
To answer your question, you can contact (e-mail) anyone by simply clicking on their profile picture (which takes you to profile page) & selecting "email" whoever..
As to your side note: NO. Doing what you want is called engaging in dialog and is against the rules. You CAN add "additional details" and respond to specific answers, but that means the person you are adding info FOR has to come back in order to see you added more information. So, NO, there is NO easy way. Simply post again and like you did here, simply add in the additional information.
Now, for the formula question: I use Excel 97, but I think it will be mostly the same. I have never wanted to set colors based on a result, but in thinking about it, I think your answer is in using "formulas as formatting criteria". Here is what I found in the HELP (an excerpt) when I looked into formatting. Apparently you CAN set the color on the fly by use of a formula. They used a conditional format to set the color of the text in a cell (in an example I did not copy here because there is no way to include the illustrations) so I think there must be a way to set the background color as well. Here is what I copied for the help:
===== start block==== Formulas as formatting criteria
You can compare the values of the selected cells to a constant or to the results of a formula. To evaluate data in cells outside the selected range or to examine multiple sets of criteria, you can use a logical formula to specify the formatting criteria.
· If you select the Cell Value Is option and compare the values of the selected cells to the result of a formula, you must start the formula with an equal sign (=). · If you select the Formula Is option, the formula you specify must return a logical value of TRUE (1) or FALSE (0). You must start the formula with an equal sign (=). The formula can evaluate data only on the active worksheet. To evaluate data on another sheet or in another workbook, you can define a name on the active worksheet for the data on another sheet or workbook, or enter a reference to the data in a cell of the active worksheet. Then refer to that cell or name in the formula. For example, to evalute data in cell A5 on Sheet1 of the workbook Fiscal Year.xls, enter the following reference, including the equal sign (=), in a cell of the active sheet: =[Fiscal Year.xls]SHEET1!$A$5
The formula can also evaluate criteria that is not based on worksheet data. For example, the formula =Weekday("12/5/99")=1 returns a value of TRUE if the date 12/5/99 is a Sunday. Unless a formula specifically refers to the selected cells you are formatting, the cell values do not affect whether the condition is true or false. If a formula does refer to the selected cells, you must enter the cell references in the formula.
====END block=====
Note above where they used the formula
=Weekday("12/5/99")=1
in the conditional format. This evaluates to TRUE or FALSE and in the conditional format, TRUE or FALSE can then make an action happen, like set the background color to blue. At least, this is what I would be looking for to set the colors based on the value of the weekday() function. In the database languages I am familiar with, and also in something like javascript or BASIC used in Excel, looking at the cells with the dates and then using a formula instead of a fixed value is the way to go.
Sorry for the incomplete answer, but I am at work and on my lunch hour and my time is up for personal business.
In the column where you need the color, click the conditional formatting arrow (it's on the home tab of th eribbon). Select "highlighe cells rules" and select "equal to". Then in the box type Monday, and then in the box to the right, click the arrow and select custom format and select whatever color you want. Set up you a rule for each day's color that you need. Then you can copy the formatting to any cells on the spreadsheet after you establish your rules.