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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Blinken86

Thread Starter
Joined
Mar 25, 2008
Messages
9
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...
 

Attachments

Joined
Feb 24, 2003
Messages
331
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

Thread Starter
Joined
Mar 25, 2008
Messages
9
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. :confused:
 
Joined
Apr 7, 2007
Messages
439
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.
 

Attachments

Joined
Apr 7, 2007
Messages
439
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.
 

Attachments

Blinken86

Thread Starter
Joined
Mar 25, 2008
Messages
9
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... :D

Your on-going help is greatly appreciated!
 
Joined
Apr 7, 2007
Messages
439
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.
 
Joined
Apr 7, 2007
Messages
439
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

Thread Starter
Joined
Mar 25, 2008
Messages
9
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. (y)

Now back to scheduling... :mad:
 
Joined
Apr 7, 2007
Messages
439
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".
 
Joined
Apr 7, 2007
Messages
439
:D - 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

Thread Starter
Joined
Mar 25, 2008
Messages
9
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. :p So good for scheduling purposes.

Big help mate! (y) And it should be showing it as solved now...
 
Joined
Apr 7, 2007
Messages
439
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

Thread Starter
Joined
Mar 25, 2008
Messages
9
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...
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top