Solved: Conditional formatting - Two conditions... One format

[Blinken86]

I need to conditionally format a cell based on the outcome of a formula and the value in another cell and I’m stuck as to how to do it.

I have a table that has the next 50 weeks of our project across the top row (row 10) and subsequent "week ending" date in the next row (row 11) and two columns (columns I and J) that I enter the start and end dates in respectively (always a 5 day working week whose end date corresponds to that stated in row 11).

For each row I have used conditional formatting based on the function (=AND(L$11>=$I14,L$11<=$J14)=TRUE) to highlight the cell on row 14 for which the dates I enter (I14 and J14) fall within the given week of the project based on the ending date of each week (row 11).

This has worked perfectly as a scheduling tool up till now however I have been asked to change it. I now require that the formatted colour be one colour when the value in a separate column (column F) is 1 and another colour when the value in the column F is 2. Any help would be greatly appreciated.

I have attached the document to help if needed and please post if it's not able to be done please.

Thanks in advance...

 

[maxflia10]

You can shorten your formula to,

=(L$11>=$I14)*(L$11<=$J14)

Do you want to add another condition? Or is this a separate issue with Column F?

 

[Blinken86]

Yes basically... I want the fuction to conditionally format a certain cell(s) on each row that corresponds to the date I enter in column "I" and duration i enter in column "H" (this automatically calculates column "J", the end date, as you've no doubt seen). This cell must be blue for when the value in column "F" is =2 and orange when the value in column "F" is =1.

 

[MRdNk]

My understanding is you want to colour cells K14:BH34 (select these cells and add the conditional formats).

If so the conditional formatting should be:
Condition 1: =IF((L$11>=$I14)*(L$11<=$J14),IF($F14=1,TRUE))
Format: ORANGE

Condition 2: =IF((L$11>=$I14)*(L$11<=$J14),IF($F14=2,TRUE))
Format: BLUE

Sheet attached.

Let me know if this is what you're looking for.

 

[MRdNk]

Just noticed that this is slightly off, and I think you're actually looking to start from Column K, not Column L; in which case it should be:

Condition 1: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=1,TRUE))
Format: ORANGE

Condition 2: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=2,TRUE))
Format: BLUE

Reattached.

 

[Blinken86]

Thanks for that mate... mucho mucho appreciato'.

I woke up last night with a flash of inspiration and thought of using =AND(L$11>=$I14,L$11<=$J14,$F14=1)=TRUE (Orange) and =AND(L$11>=$I14,L$11<=$J14,$F14=2)=TRUE (Blue) but your equations are a lot easier to check.

It works great but I've run into another problem now (sorry to be a pain). I've changed the "Finish Date" column to show N/A if the values in the "Start Date" and "Duration" columns are equal to 0 (manager's request). I've used =IF(H14=0,"N/A",I14+(H14/5)*7-3) for the end date on row 14 for example.

With this change i've noticed now that if the values in the kick-off Information cells (columns H,I and J) is equal to 0 then the whole row becomes highlighted. Is there a way around this so that if the information is =0 then the cells are left blank? Or should i suggest he just sucks it up and stop being such a pedantic princess about it?

Unfortunately i can't just filter all the 0's away as for some unknown reason he also wants to see who isn't going on each trip - management quite hasn't worked out how to use the auto filter...

Your on-going help is greatly appreciated!

 

[MRdNk]

No problem - bosses hey!

Conditions should be as follows:

Condition 1: =IF($H14=0,TRUE)
Format: (NONE)

Condition 2: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=2,TRUE))
Format: BLUE

Condition 3: =IF((K$11>=$I14)*(K$11<=$J14),IF($F14=1,TRUE))
Format: ORANGE

- I would just copy and paste Condition 1 into Condition 3, and add the new condition into Condition 1. Its important that its in that order.

 

[MRdNk]

Actually, a better answer is:

Condition 1: =IF($H14<>0,IF((K$11>=$I14)*(K$11<=$J14),IF($F14=1,TRUE)),FALSE)
Format: ORANGE
Condition 2: =IF($H14<>0,IF((K$11>=$I14)*(K$11<=$J14),IF($F14=2,TRUE)),FALSE)
Format: BLUE

 

[Blinken86]

Perfect! So simple really. This is what happens when you're given too much work... You forget to use your head...

Cheers heaps mate! Once again appreciate your help with this. Your a champ.

Now back to scheduling...

 

[MRdNk]

And you can use "H" or change to "I" if you'd rather base it on that Column; or if you want to base it on "J" then you also need to replace "<>0" with "<>"N/A".

 

[MRdNk]

- No problem, I've had to Scheduling like this in the past.

Ps. Don't forget to add "Solved:" to the title of your original post.

 

[Blinken86]

Your right actually. This is a very handy addendum.

Helps a lot considering our billion dollar company doesn't want to upgrade to the new microsoft office so excel is still limited to three conditional formats.

Gotta love the new excel... filtering by color... 30ish conditional formats. So good for scheduling purposes.

Big help mate! And it should be showing it as solved now...

 

[MRdNk]

Well I'll let you get back to work.

Colour filtering is a great new addition, have had to write a macro to filter by colour in Excel 2003, as my £ Billion company hasn't upgraded to 2007 yet either.

 

[Blinken86]

I can never figure it out... Mhe... i've just got paid for finding this solution so if their happy to pay for it then let them stick with 03'.

Once again a great help and hopefully someone else gets some help from this...