How to add value to conditionally formatted cells in excel

Hi,

I am running a time chart, which has a start date/end date & on basis of few conditions conditionally format cells b/w this time period.

eg:
start date 10th Jan/End date 10th Mar. Now it selects cells from 10th jan to 10th feb & color as green because till this time period all tasks are done. and leave rest from 10th feb to 10th mar in red as not done yet.
So far its working fine but now I want it to merge those cells which are green and put a value on top saying 220 tasks have been done & merge red to say 220 left.

Please help How to do this using VBA or what ever best possible way?

Thanks in Advance.

Vikram

Vikram, can you please upload a smaller file? A sample of your data with just a few rows would be sufficient.

zip it, then.

vikram, this is a rather complex undertaking. I'm strongly against using merged cells, since they cause all kinds of trouble.

Why not use a project management software that was designed to do these things out of the box instead of forcing Excel to be something that it is not?

I have spent some time looking at your workbook, and a few things aren't clear:

I don't see why there are two Status columns, how their values are used, or if that's even important for your question.

I don't see why you need to merge cells.

I don't understand where the number 220 came from in relation to how many tasks are completed, or left.

Although I might be able to answer your question on its face, I agree with Teylyn that a project management tool would do a much better job of this, and probably do what you need out of the box with no special configuration or programming on your part. There are lots of free open-source products if you don't want to buy a commercial tool like Microsoft Project.

Going down this path will cause you write Microsoft Project in VBA, and will be more complex that you may expect.

I don't see the answers to any of the questions I asked. Also you make references to the "red part" but there is no red part, and there is no conditional formatting that would make any part red.

Originally Posted by vvvv4114

Every week a status report is generated saying that all green + Red part is work done so far & red part says amount of work done last week.

I do not see where you capture enough information to tell what work that was done last week. What does "done" mean--there is progress on a task? The task is completed? How are you expecting to determine that for formatting purposes?

saying like project started in jan till current situation say may and even last week cells get colored too. and left out is all blue from may till aug.

I'm sorry but I can't parse that at all.

Now I am trying to add values to it like will add three columns to the sheet...

Where are you expecting to add columns? The columns are dates, so you can't add new dates right in the middle.

...for values and trying to merge colored cells so that I can add those values to these 3 colored sections...........

I can't parse this either.

I hope you will get a clear picture of what I am trying to acheive, if not let me know will try to provide more details.....

It's not so much that I need more detail, but I am having a hard time with your English, punctuation, and capitalization. I have only the vaguest idea of what you are trying to do and how you want to implement it in Excel. It would be better to explain what value you are trying to get out of this tool before diving into the details of the tricks you want Excel to perform.

What do you need to show the audience for this information?