Conditional formatting to highlight a completed task in adjacent column

[Mrs Bacon]

Hi

I have a worksheet set up with columns for tasks, dates and a status (e.g. 'complete') I have set up conditional formatting on the date column so that the date is highlighted if it is within or over the next seven days. What I'd like is to add a formula so that the highlight disappears (or is a different colour) when the status is changed to 'complete'.

I've been trying a few things but I'm newish to this function and am getting a bit lost. Can anyone help please?

The current rules in the date column (column D) are:

=ISBLANK(D1)=TRUE
<NOW()
<NOW()+7

The status column is column E.

Thanks!

[DBY]

Hello welcome to the forum.

Try the following Conditional Formatting formula in your range of cells in column D:

Please Login or Register  to view this content.

DBY

[Fotis1991]

Welcome to the forum. Pls take some minutes to read forum rules because:

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[Aardigspook]

Welcome to the forum.

DBY's interpreted your 'within 7 days' differently from me. I assumed from your current rules that you want to know if a date is within the next 7 days (possibly with different colours for 'Today' and 'up to 7 days'?), as against 7 days either side of Today, which is what DBY's formula does.
One way to do what you want (if I'm right) is to have three rules for column D, applied in this order:

Please Login or Register  to view this content.

The attached file shows this working on Sheet1 (DBY's is on Sheet2 for comparison).

Hope one of the solutions works for you.


Edit: @Fotis1991 - sorry, I was drafting this and posted it before I saw your message.

[Mrs Bacon]

I hope that the title is now acceptable.

Thanks DBY, I have tried that formula but it hasn't worked. I entered it as a new rule and 'use a formula to determine which cells to format', both with and without 'stop if true'. (Sorry if it's unnecessary detail to describe what I did but as I say I'm new to this and not sure if I'm doing something obviously wrong.)

Both times it highlighted dates at random - some dates which haven't happened yet but none which have already gone AND have 'Complete' in column E. It also highlighted some dates which had 'In progress' next to them instead of 'Complete'.

[Aardigspook]

Welcome to the forum - the title is now more descriptive.

I interpreted your question differently from DBY. I think what you want is a date to be highlighted if it's in the next 7 days from today, yes? As you mention <NOW() separately from <NOW()+7, I suspect you would also appreciate a different colour of highlight for a date which is already past?

Here's the CF formulae I suggest:

Please Login or Register  to view this content.

Does that do what you want?
If not, can you please upload a sample file (without any confidential information), showing what you want to happen? Thanks.

(Attached is a file with the above CF working, with a few examples.)

[Mrs Bacon]

Thanks Aardigspook, that is just what I mean!

The example you attached is great and would be perfect. However it hasn't worked the same on my sheet - I'm thinking I must have set the initial sheet up wrong or something. I've attached what happened when I tried it - anonymised the tasks though.

I'm probably doing something very simple very wrong - thank you for your patience!

[DBY]

@ Mrs Bacon
I didn't get back because of the Moderator's warning. I'm assuming all's now okay. I think I misunderstood what you require but Aaridigspook has uploaded some nice examples. Take a look at those and perhaps post a sample file as suggested.

*Note. Sorry just seen your last post.

DBY

[DBY]

I hope Aardigspook doesn't mind me coming back in here but I've amended your example file. The formulas were referencing row 3 instead of 2 and were out of sync. I've also added and AND argument to them to account for blank cells and changed the order of conditions.

Hope this does as required.
DBY

[Aardigspook]

No problem, just a couple of tweaks will fix it:

1. Your CF formulae all refer to row 3, not row 2. Go to 'Conditional Formatting' then 'Manage Rules'. For each rule in turn, click 'Edit Rule' and change D3 or E3 (as appropriate) to D2 or E2. Once they're all changed, click 'Apply'.
What was happening was that the rule was looking at the row below (for example, D4 was being coloured based on the date in D5).

2. Change the order of the rules. Still in 'Manage Rules', use the up/down arrows (to the right of 'Delete Rule' to put the rule for 'Complete' at the top, then the rule for D2 < TODAY(), then lastly the rule for D2 < TODAY()+7. Click OK and that should fix it.

I suggest you add another rule as well, to have the date column without a colour if there's no sub-task. Use this:

Please Login or Register  to view this content.

Put it at the top of the rules, with 'Stop if True' selected.
Any blanks in column D will still show red if there's a sub-task in column B, but I assume that's okay because that'll highlight that there's no date been entered.

Finally, I suggest you don't apply the rules to the whole column - i.e. change the 'Applies to' from =$D$2:$D$1048576 to =$D$2:$D$1000 (change the $1000 to whatever range suits your data). Making Excel process a whole column can slow it down - maybe imperceptibly, but sometimes enough to notice if your file gets larger.

Attached is your file back with all of the changes above applied.

Hope that all helps.

[Mrs Bacon]

That's brilliant, thank you both so much for your help!

[Aardigspook]

You're welcome, glad we could help. If that's solved your problem, please mark the thread as Solved so other forum viewers can see that (go to your first post, click 'Thread Tools' and select 'Mark this thread as solved') - thanks!

I hope Aardigspook doesn't mind me coming back in here

@DBY: I was the one who jumped in after your reply, so it would be a bit hypocritical to object!

[Mrs Bacon]

I did notice the 'solved' in other threads and wondered if I needed to do anything - cheers!