excel help

maxamos

I'm trying to create a spreadsheet at work to enable us to keep on top of certain applications/requests we need to do before a job starts. I would like to highlight the name of the job in different colours depending on the time from the starting date i.e. we need to apply for agreement six weeks prior to a start date i would like the background of that job to show green seven weeks before and to turn red when there is less than six weeks.
Does anyone know of the correct formulas for this.

We have several diffent time scales for various jobs (6 weeks, 7 days, 3 days) and I would like to be able to enter something into a column that would automatically relate to the specific timescale.

Any help would be very much appreciated

Max

Wombat

You need conditional formatting on the cell(s) you want to change colour.

Here's someone with the same question (and the answer):

http://www.mrexcel.com/tip054.shtml

More info/examples here:

http://www.contextures.com/xlCondFormat01.html

HTH

maxamos

Thanks for that I'll have a read through the guides and try them out.

Just need to work out the formula to give the values now

Max

EvilMonkey

Just a quick point about conditional formatting, it's limited to 3 conditions. If you've more than that (I'd say these are the types of jobs with different timescales you mentioned) CF may not be the answer.

If it isn't then I think it's probably into the macro sphere. Not too bad, you could put an auto open macro in to run everytime its opened to do something similar. It's been a while since I did it, but I have done something like this in the past. Repost if CF isn't working and I'll post some of the code up for you (it wasn't complex honest)

E.M.

Fairwinds

With your start date in Col A, and your time scale in col B (42days or 7 days etc), highlight the cells in the row that you want to conditionally format.

From the format -> conditional format menu, change the option from cell value is to formula is.

First condition (for row2) is then =TODAY()+$B2>=$A2 select background pattern to be red.

Add a second condition as =TODAY()+$B2<$A2 and select background colour as green.

Probably then easier to use the format painter (paint brush ) to apply the format to all rows that you require.

Hope that helps

P.S Evil monkey is correctabout the limitation of 3 colours with conditional formatting, however you can use the base colour of the sheet as another colour which effectively allows you 4 colours.

AndyRat

The are other ways to get more colours, you can use the formatting of numbers to provide three more there was a post regarding this a few months back, sorry, I don't have time to search for it right now :-(

And as previously mentioned, you can use macros to format as well, increasing the colours further.....

HTH

maxamos

Thanks for all the answers

I was thinking about creating a value of either 1, 2 or 3 depending on the how many days were left and link the conditional formatting to those values ie 1=green 2=yellow and 3=red, and then have a formula to create this value dependent on which application was required ( ihope that makes sense).

Does that sound feasible or would be a bit long winded.

Cheers

Max

Fairwinds

AndyRat wrote:

The are other ways to get more colours, you can use the formatting of numbers to provide three more

HTH

I don't believe that is correct, you can format the fonts as well, but only within the 3 conditions. It doesn't give you 3 more conditions

Maxamos wrote:

I was thinking about creating a value of either 1, 2 or 3 depending on the how many days were left and link the conditional formatting to those values ie 1=green 2=yellow and 3=red, and then have a formula to create this value dependent on which application was required ( ihope that makes sense).

Does that sound feasible or would be a bit long winded.

HTH

That sounds completely feasible. You may want to try and get the two condtions working first - have you tried what I suggested. did it work for the two conditions?

sloughflint

I could swear I created a formula with nested conditions a while back with more than 3 conditions. I'll have a root round later and see if I can find it.

Fairwinds

You can use if statements, to nest functions, but you can't format for more than 3 conditions (without using VBA)

AndyRat

Just found the old post I referred to yesterday.....

http://forums.moneysavingexpert.com/showthread.html?p=1550516#post1550516

That might help.....