Order of Conditions for Conditional Formatting

[jpruffle]

Hi, this is my first post and I am relatively new to excel so please bare with me. I have a worksheet that has the following information:

Column A Due Date
Column B Status

When column A is within a day of today's date the row (which contains other headings) needs to turn the font Red. At present I have done this via conditional formatting using Formula is

Please Login or Register  to view this content.

on it's own this works.

When column B is entered as Finished I want that entire row to turn Green. So I have used conditional formatting using Formula is

Please Login or Register  to view this content.

this works in conjunction with condition 1.

However the final part of this is I need every second row to show a blue background colour so the data is easier to read. Again I have added the conditional formatting using Formula is

Please Login or Register  to view this content.

on it's own this works but not as a condition added with the other two.

Can anyone advise me on how I can run these three conditions together? I need every second row blue. If it is past it's due by date the font goes red. If the status is change to Finished the font goes green.

Thank you in advance.

[oldchippy]

Hi,

Change the order you want the process, the third first, first second, second third.

[jpruffle]

Hi,

Thank you for the quick reply. I don't think I explained myself very well. No matter what order I put the conditions in as soon as I add the MOD row it doesn't work. Either the font doesn't change colour or the row colour won't change.

I wondered if it was possible to set conditions using a different formula / VBA etc that might cater for what I need:

Condition One - Every other cell to be x backgound colour
Condition Two - If the task status is Finished all cells in that row to be green font.
Condition Three - If the due date is past todays date show red font unless the task status is Finished.

If neither condition Two or Three are met for the text to be black, with alternate row colours?

Thanks

[oldchippy]

Since I posted my reply, I thought I'd better try it myself. You were right it is a difficult one, but I think I now have the answer

First of all, click the little square in the top left hand corner to select the whole sheet, then apply CF in the "Formula as"

=MOD(ROW(),2)

Then select row 1 and delete the CF from that row

Select A1 default the cell colour to Blue background, by "Format cells"

Then apply CF to A1, condition 1

=IF(OR($A$1="",MOD(ROW(),2),$A$1>TODAY()-2,$B$1<>"Finished"),) apply Blue background

condition 2

=$A$1-TODAY()<2 apply Blue background, Red bold text

condition 3

=$B$1="Finished" apply Green background

Then select B2 and apply CF, condition 1

=IF($B$1<>"Finished",MOD(ROW(),2)) apply Blue background

condition 2

=$B$1="Finished" apply Green background

Now to get the remainder of row 1 to turn Blue or Green, click on B1,click the Paintbrush icon, and apply it to C1:IV1

Just in case a copy of the sheet is attached, so that you can see yourself.

Hope this is what you want.

[jpruffle]

Thanks oldchippy,

Didn't quite work how I wanted it but it has really got me on the right track thanks for now. I'll have a good run through it over the next few days and I'll let you know if I struggle.

Really appreciate your help and advice.

JP