Conditional Formating

[rizmomin]

Hello:

Please refer to attached file.

I need to apply the conditional formatting as explain below.

I have data in Cell B3:K12 which shows if the task listed in column A are completed or not ( 1 mean completed, 0 mean not completed)
It's 12 digit number consist of 0 and 1.
Each digit is referring to each month of the year.
(1st digit is for Jan, 2 for Feb .....12th for Dec)

Basically 1 means task is completed and 0 means task is incomplete

So Example :

A3 = 111000000000 means task for task#1 for Jan,Feb and Mar are completed and Apr thru Dec not completed.

Based on Current month which is in cell C1 (April), all task upto Mar should have been completed.
So all numbers should be 111000000000.

I need to highlight all task which has been completed Green.
The task which are not completed needs to be highlighted Red.

If current month is May then for quality all task completed the number would be 111100000000 and then apply CF accordingly.


Let me Know if you have any questions.
Thanks.
Riz

[Special-K]

Try

Select B3
=(LEFT(B3,$C$1)=REPT("1",$C$1))
format as Green

Select B3
=(LEFT(B3,$C$1)<>REPT("1",$C$1))
format as Red

Use Format Painter to copy to the other cells

This assumes that if Jan Feb Mar or Apr are incomplete and we are in May then the result is incomplete (because Jan Feb Mar or Apr is not complete yet)

[FDibbins]

Here is another option
1. format all cells green (then you only nered 1 rule)

2. highlight the range you want to apply the conditional formatting to
3. on the home tab, styles, select CF
4. select new rule, select use formula
5. enter =LEN(SUBSTITUTE(B3,0,))<>MONTH($B$1)-1 format fill RED

If you feel the need to CF the cells that are correct (instead of formatting all cells green)...
6. repeat 4 and 5 with...
=LEN(SUBSTITUTE(B3,0,))=MONTH($B$1)-1 format fill GREEN

[rizmomin]

Hello Special-K:

Thanks a lot for great help.
Your formula works great.
Riz