How can I make it so excel shades cells automatically between two dates?

Question

I am working on a construction project and I want to show how long certain tasks will take. for example a row in my sheet may look like this...

Start, Finish, (start and finish should be above 7/6/11 and 7/27/11)
Name of project, 7/6/11, 7/27/11

I want to set it up so that everytime those start and finish dates are changed, excel will automatically shade the cells purple between the new start and finish dates. HELP!

Also, there is a single date in each column in row 8 and the dates go for 3 months. What I want to happen is I want excel to shade the dates between the start(column E) and finish(column F) dates according to the dates that are in row 8

The row I am trying to get shaded between two dates is 132

If you need more info I will be checking answers so I can always add more!

Answer 1

Hi,



I don't fully understand what you're asking but this should give you a start:



http://www.mediafire.com/?zd4dd3sdmeldhb7



Basically it is using conditional formatting where C1:L1 contains the dates (extend as far as needed) and Col A and B feature the start date and end date respectively.



In the above example A3 and B3 contain the start date. The conditional format applied to C3 is:



=AND($A3C$1)



Format the cell as needed (Change the "<" and ">" parts to "<=" and ">=" if you want the start and end dates highlighted too otherwise only the inbetween dates will be highlighted).



Copy this conditional format across to all of the required cells.



Hope this helps to start.



Thanks







Hi,



To apply the conditional format to multiple rows the easiest way to do it is to copy the cell which contains the conditional format, highlight all the cells you want to apply the formatting to then right click and select pastespecial. Choose formats and click ok.



In order for it to work correctly your conditional format needs to have part absolute referencing in the formula which is represented by the $ sign.



The $ sign locks the following column or row reference so it doesn't update when you're filling it down or across.



So if you have $A1 and filled to the right and down all of the formulas would have the A but the row would change where needed: 1,2,3,4,5,6...



If you have A$1 and filled across and down the columns would change where needed A,B,C,D,E... but the row would remain fixed at row 1.



$A$1 locks both the column and row so filling across or down would keep A1 as the cell.



SO in answer to your question how I used the same formula it is because of the absolute refence locks I used. If I didn't use part locks then it wouldn't work.



Hope this helps. Otherwise feel free to email me a copy of the workbook or upload it to mediafire.com and post the link here.



Thanks

Answer 2

Hi,



Ok, a bit different from your last question..



If you're manually entering start and end dates in, say, E2 and F2, then with your dates in row 132 (the row you want highlighted):



Select row 132, conditional format>use a formula>

type in: =AND(A$132>=$E$2, A$132<=$F$2)



If your start and end dates are in different cells, change the cell references in the CF formula to the appropriate cells.



Manually entering start and end dates do not require the OFFSET, INDEX, MATCH formula from your previous question.



If you have any other questions about this, feel free to email me with more specifics.



Hope this helps.



re: Additional Details: Yes, depending on if the other rows will use the same start and end dates or if you are using 2 different dates, so that you have 2 sets of start/end dates.



For the first option, using the same start/end dates, click on Conditional Formatting>Manage Rules

Then click the rule, and where it shows Applies to: CTRL-click your other rows that you want the rule applied to.



If you're using a separate set of start/end dates, just create a new rule and change the formula to specify the source cells (instead of E2 and F2, maybe the 2nd start/end date entries are in G2 and H2, and change the 132 your new row. Make sure to leave the $'s or the CF formula won't work properly.

Answer 3

hi, ok, somewhat specific out of your very final question.. while you're manually entering start up and end dates in, say, E2 and F2, then collectively with your dates in row 132 (the row you want highlighted): opt for row 132, conditional format>use a equipment> kind in: =AND(A$132>=$E$2, A$132<=$F$2) in the adventure that your start up and end dates are in specific cells, distinction the cellular references interior the CF equipment to the properly suited cells. Manually entering start up and end dates do no longer require the OFFSET, INDEX, tournament equipment out of your past question. in case you have gotten yet another questions approximately this, evaluate loose to digital mail me with extra specifics. desire this is assisting. re: further information: specific, consistent with if the alternative rows will use the comparable start up and end dates or while you're employing 2 specific dates, so as which you have have been given have been given 2 instruments of start up/end dates. For the popular selection, employing the comparable start up/end dates, click on on Conditional Formatting>take care of rules Then click on the guideline of thumb, and wherein it shows Applies to: CTRL-click on your diverse rows which you basically want the guideline of thumb applied to. while you're employing a separate set of start up/end dates, in simple terms create a sparkling rule and distinction the equipment to specify the provision cells (instead of E2 and F2, probable the 2d start up/end date entries are in G2 and H2, and distinction the 132 your new row. Make specific to pass away the $'s or the CF equipment will possibly no longer artwork accurately.

Answer 4

Dating is among the largest contributors to the effective building of the relationship. In the internet dating arena, singles dating is thought to be hot and happening.



My online experience as single is started here: http://www.singlesdating.us is 100% FREE to join.



There are many websites that provides you with a few of the great Singles dating tips that helps you to zero in on the appropriate partner for you. Those people who are not committed to any kind relationship presently may make use of these dating tips for their advantage. It provides just one status person with a path to be treaded easily and effectively to accomplish good results in this dating field.



One of the greatest coming from all tips is to project that you are single in your profile. Single status is thought to be a precious one and most of them who opt for dating want to date a person with single as status than whoever has a committed status. Many people hate to hang out with a committed person as they may be averse to thinking about any conflicts in a relationship. So, being single is an excellent advantage in itself and all you need to do is to follow a number of the basic tips to nurture an awesome relationship.



Yet another crucial aspect of all other dating tips is always to join groups of sole. There are many sites and forums that are made bearing in mind the needs of this singles alone. Only singles are allowed membership in such sites. If you are just one and are really purchasing for a single status person you'll be able to join these sites and locate the perfect match for you personally through these sites. In these sites, you will find a lot of Singles dating tips and also the experience encountered by some other singles. These tips will serve to become of immense use to suit your needs.