Noob question: Conditional formatting

[nothingbutsound]

Hi all, Ill admit I dont know much about Excel, it scares me!

How ever I need to use it! My question surrounds Conditional formatting, I want to create a tracker with modules, when a module is complete I will enter the date. Each module has its own column, the last column is called "complete" and this is what I would like help with, once each module has a date entered, the "Completed" column cell turns green or red depending if complete or not.

I have attached an example of how I would like it to look. I am using Excel 2013, is this possible? if so how?

Thanks in advance all!
Dan

[AliGW]

Try this:

1. Create a CF rule using the formula =COUNTIF($E4:$L4,"<>")>0 for red.
2. Create a CF rule using the formula =COUNTIF($E4:$L4,"<>")=0 for green.
3. Set the applies to box for both rules to $M:$M.

[63falcondude]

Hi and welcome to the forum

You want to CF the completed column when a date is entered into which column?

[Glenn Kennedy]

Yes. Like this, with two CF formulae:

=ISBLANK(E2)

and

=E2="Complete"

aplly to the range of cells (E2 to E20, in the attached sheet). Adjust the cell refs to suit (I couldn't see your pic clearly).

[nothingbutsound]

I would like it to say complete when the all the cells from E$4 to L4 have a date entered, and if one dat is removed it changed to not completed.

[AliGW]

That is what the CF rules I gave you should do - did you try them?

[nothingbutsound]

thank you so much for getting back to me so fast, I tried what Ali and Glenn (thank you for sending a example, legend!) unfortunatly I cant get that to work. I may of articulated it incorrectly. If any cell between E4 and L4 dont have a date entered the "completed" column remains red, once a date is entered in all cells between E4 and L4 it changes to green.

[AliGW]

Please attach your workbook here. I understood perfectly what you want. If you provide the workbook, I can troubleshoot it for you.

[63falcondude]

Since you want 2 colors, you will have to create 2 rules. Try this:

Rule 1:
Highlight M4:M25 > Conditional Formatting > New Rule > Use a formula
=COUNTIF(E4:L4,">0")=8
Format: Fill green > OK > OK

Rule 2:
Highlight M4:M25 > Conditional Formatting > New Rule > Use a formula
=AND(A4<>"",COUNTIF(E4:L4,">0")<>8)
Format: Fill red > OK > OK

[nothingbutsound]

Hey Ali, thank you so much for looking at it for me! ive attached the workbook

[63falcondude]

Note that September 31st (E4) is not a valid date.

[AliGW]

There is nothing to troubleshoot. I asked to see the workbook where you had tried to use the CF formulae I gave you - please attach that version.

[nothingbutsound]

Sorry Ali I misunderstood you. Ive attached the version to where ive tried to apply the advice you gave me, initially it looks like its working but when you add data to the cell it doesnt change the completes cell.

[AliGW]

You'd got into a bit of a pickle! Attached is an amended version.

[nothingbutsound]

Ali Thank you so much! out of curiosity what was the pickle (Story of my life lol)

[AliGW]

Well, you'd got lots of other rules conflicting and you had the colours the wrong way round. I amended these things and then I needed to add an extra bit to the second rule to make it format the cell only if column A contains data, too. You had also left the manual cell colouring in place, which was in the way.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.