Overwriting the Format condition

[chamdan]

Help needed,

Is there a way to overwrite the format condition formula should a particular condition be met?

Knowing that format condition gives only three conditions, how can you overwrite it by other conditions in case a forth condition comes up, which is not part of the other three.

Cheers!

[TMS]

If what you are asking is, how can you have more than three conditional formats in Excel 2003, your options are set a fourth condition as the "norm" or default, use VBA with worksheet change event or upgrade to Excel 2007/2010.

Regards

[chamdan]

I am giving colors to columns that corresponds to a WBS level, which is for instance:

Level 1: Color (Black) the columns let us say F1:IV1
Level 1.1: Color (Brown) the columns F2:IV2
Level 1.1.1: Color (Light Blue) the Columns F3:{Corresponding Column refering to a particular finish date} i.e. S3

Knowing that certain columns will be greyed due to weekends Saturday and Sunday.

Here is an example:
A B C D E F G H I J K L
1 WBS Task Duration Start Finish
2 1 ABC 7 d Oct 1 2010 Oct 12 2010 Black Black Black Black Black
3 1.1 Abcd * 7 d Oct 1 2010 Oct 12 2010 Brown Brown Brown Brown Brown
4 1.1.1 cde 3 d Oct 1 2010 Oct 5 2010 Blue Grey Grey Blue Blue
5 1.1.2 efg 4 d Oct 6 2010 Oct 11 2010 Blue Grey Grey Blue Blue
6 1.1.3 fgh 0d Oct 12 2010 Oct 12 2010 ▲ <----- the forth condition is this, since it is a subtask
but because the duration is zero (Milestone)
Place a Diamond or whatever.

[chamdan]

Hi,

Enclosed is a sample of what I am trying to do.

Cheers!

[chamdan]

Hi,

I made the appropriate sample spreadsheet that would reflect what I am trying to resolve. Should another solution be available to do what I am trying to do.

Thanks again in advance!

Chuck

[chamdan]

Dear TM Shucks,

I am not a VBA expert but try always to manage within my knowledge of Excel to do the best of my ability. But sometimes a VBA routine may solve the concern I have but help would be required to do what I am trying to achieve. I have uploaded an example (second upload) is the one I need to resolve. Can you or anybody out there help me find a solution to it?

Your help is greatly appreciated in advance.

Regards,

[TMS]

It looks as though you could add another test to the AND in the second CF Formula,

Please Login or Register  to view this content.

Could be amended to:

Please Login or Register  to view this content.

Regards

[chamdan]

Sorry, but this is not working the way I am expecting. What I need is to be able to change the background color and put the

"▲"

where the milestone falls. This means that this triangle should show at Column "N" and Row 9 only because the the start date and the end date correspond to the position N9. Refer to the example. On the other hand, I have learned something new from your code is that you can have more than one condition within the same AND.

=AND(N$3>=$D9+($E9-$D9+1)*$F9,N$3<=$E9,$C9<>0)

[TMS]

To my knowledge, you can't insert characters with CF, you can only change the formatting.

You would need a formula in the cell itself, similar to the one you have in your example but date dependent (as is the CF), to position the triangle.

Something like:

=IF(AND(N$3>=$D9,N$3<=$E9,$C9=0),"▲","")

Regards

[chamdan]

Thanks TMShucks! I guess I will have to find some other way to meet my request. But I am still not convinced that there are no other tricky way to do that.

Appreciate your time and thanks for your replies. Do I have to close this post? but what option should I use since it hasn't been resolved?

Cheers!