conditional formatting?

[jjjjjjjjunit]

Hi all,

I'm a bit of a noob to excel programming, but have used it extensively for data analysis during my doctorate...

I am not working on a CRM system for a company and have 3 sheets - 1)clients info & notes, 2)sales log, 3)Orders

I have used conditional formatting to highlight different orders (rows) in different colours depending on how close to the due date we are. I need to have this overrided so that when I input a completion date in the cell next to the due date, the entire row becomes grayed out.

I cant seem to get this last part right..

any help would be great

thank you

[Naveed Raza]

could u please provide sample workbook with desire output

[jjjjjjjjunit]

orders sheet.xlsx

Has that attached?

What i would like to happen in the end is for the cell in column B to change coloure from green to orange to red based on how far away we are from the "advised order completion date" but once I have entered a value in the "order conmpletion date" column (C) I want that entire row to be coloured in a medium grey (to indicate that it is completed) and for the green/orange/red colour to no longer be applied.

I was thinking therer should be a rule in the begining that says - if C1 has value, then row 1 is gray. stop if true. then as long as there is no value in column C, the other colour rules can be applied...

Does this make sense? sory if my terminology is wrong.

J

[popipipo]

=AND($B5<TODAY();$B5<>"")
Try this kind of fomulas

[jjjjjjjjunit]

Poppino, Could you provide a little context for your suggestion?

I really am a novice, I dont really see how it addresses my current issue

[popipipo]

Take a look at this

[Richard Buttrey]

Hi,

What popipipo is saying is that instead of using the Cell Value is option in the conditional format use the Formula Is option.

e.g.
Where you have cell value less than NOW()

use instead the logically equivalent formula of

Please Login or Register  to view this content.

but extend that to pick up the column C condition of not blank. i.e.

Please Login or Register  to view this content.

Then create a new rule for the whole row to be grey when B5<NOW() and $C$ <> ""

[jjjjjjjjunit]

thats great, I already had that going, but only on a single cell...

Could you explain how its applied the formatting to the entire row?

Also how would I get this to cover my initial issue? once I input a date into Column B, the order is complete, I then want the entire row to be coloured in grey.

Do i need to put a rule first so that it checks to see if column C has a value, If it does, then it colours the whole row grey, if it dousnt then it moves on to the next rules.

[Naveed Raza]

Hi

have a look to the attached file

[jjjjjjjjunit]

Hi

have a look to the attached file

Thank you very much, that's exactly what I was trying to do.

I think I need to scrub up on the codes, so that I really understand what's just happened. Can you recommend any good reading to get me going?

[Naveed Raza]

Hi

Glad to help you

if u satisfy with solution then please mark the thread as SOLVED and please don't forget to click on *Add Reputation to say thanks

Go through this

http://www.excelforum.com/excel-prog...materials.html