Conditional Nest statements

[bknight]

Hans, thanks for the site:
File included Note that workbook_Open and Worksheet_Change macros still included.
Have fun.

[HansV]

Welcome to Eileen's Lounge!

For others: the original question is in Issue with worksheet change macro. I stated that the problem could be solved with conditional formatting.

You had applied the conditional formatting to cell B2 only, not to the rest of the cells. You have to select the entire range before setting up conditional formatting.
Since you have empty cells in between the ones with dates, we have to use conditional formatting with a formula:

=AND(B2<>"",B2>$J$1)

and

=AND(B2<>"",B2>$J$1+75)

See the attached version. I added two demo rows with dates in September/October, to show the rule with $J$1+75 in action.

DividendCalendar.xls

[bknight]

Yes the conditional statement was only in one cell, no need to copy format that doesn't work on is not logical to what I seek.
The formula still doesn't work on the less than dates, the greater does work.
4/20/2016 + 75 =7/4/2016 Should be yellow, but isn't
4/19/2016 + 75 =7/3/2016 Should be green, but isn't
4/18/2016 + 75 = 7/2/2016 Should be green, but isn't
4/21/2016 + 75 =7/5/2016 Should be yellow, but isn't

[HansV]

I'm afraid that I still don't understand what you want. Today is the 8th of July. The 18th, 19th, 20th and 21st of April are all more than 75 days ago. Why should the 18th and 19th be green and the 20th and 21st be yellow?

[bknight]

Target date------->Green------>+75-------->Yellow
4/20/2016-------->Green------->7/4/2016----->Yellow from 7/5/2016 forward

[HansV]

But you want to compare to J1. This cell contains a formula that returns today's date.

What and where is the target date?

[bknight]

For the first target date B2, J1 is Today, yes formula, does that make a difference with conditional formatting?
Second target date B3 etc.

[HansV]

Once more:

April 18 + 75 days = July 2
April 19 + 75 days = July 3
April 20 + 75 days = July 4
April 21 + 75 days = July 5

Today is July 8. This is more than 75 days after each of April 18, 19, 20 and 21. Why should April 18 and 19 be yellow, and April 20 and 21 green? I simply don't understand.

[bknight]

Target date------->Green------>+75-------->Yellow
4/20/2016-------->Green------->7/4/2016----->Yellow from 7/5/2016 forward
Today 7/8/2016 should be Yellow as would 7/7/2016, 7/6/2016, 7/5/2016.
7/4/2016 should be green as it is at the threshold 7/3/2016 back to 4/20/2016 would all be green

EDIT: Not to confuse you but the 75 day is rather arbitrary, it could be any value from 61-90.

[HansV]

If the 75 is variable, where do we get it from?

[bknight]

Simply my choice.
You could set up any procedure for any number (say 70 days etc. )

[HansV]

I still find it extremely confusing. Does this do what you want?

You can change the interval in cell J2, so that you don't have to change the conditional formatting formulas if you want a different interval.

DividendCalendar.xls

[bknight]

"today's date"(J1)B2-->4/20/2016->7/4/2016 B2 all Green(start date+75)-->7/5/2016 B2 Yellow(start date+76)-->7/20/2016 (start date+91) B2 Yellow:Should never exceed 91
B2:4/20/2016(start date)

Perhaps this is clearer?

[HansV]

Sorry, no.

[Rudi]

Good morning (or afternoon),

I know I am coming in late on this, (and I have not studied up the posts in the Microsoft forum), but from what I have read through this thread, I too cannot grasp the conditions where these dates must reflect appropriate colours. It would help if you can set up NEW sample with a pre-result sheet and a post-result sheet that reflects the end result you want to achieve (that includes comments that explain the condition for the colour change). The reason for asking this is because the dates in the current sample in this thread do not even match the explanatory dates in the above posts.

It does not help us (to help you) if the sample file is unreflective of the problem and solution you need. It is up to you to provide clear instruction and clear concise replies. We are always happy to lend assistance and trust that we can assist in resolving the problem with you, but it does help if we can relate to your actual issue.

TX

[bknight]

Thanks for taking the time to post and ask a question. I could post another worksheet, but I doubt that the would be beneficial since all of the criteria are not represented. So let me try to build the scenario for you.
In column B there are a number of start dates B2 is the first B1 being a header. In J1 there is @Now(). Lets start with an example that I have used earlier 4/20/2016 (in B2)
For this example lets say that todays date is 4/20/2016, then the interior of cell B2 would be green and it will remain green until the date in J1 is 7/4/2016 (start date +75). When J1 is 7/5/2016 the B2's color will change to yellow and will remain yellow, until the date in B2 changes. The table didn't turn out like I wanted.
Start Today Days from start Color B2
4/20/2016 07/03/16 74 Green
07/04/16 75 Green
07/05/16 76 Yellow
07/06/16 77 Yellow
07/07/16 78 Yellow
07/08/16 79 Yellow
07/09/16 80 Yellow
07/10/16 81 Yellow
07/11/16 82 Yellow

[HansV]

The problem is that you're explaining it the wrong way round. We cannot change today's date, but we can change the dates in column B. So you should have provided a series of examples in column B and tell us how they should be colored based on the current date.

But if I understand you correctly, the workbook that I posted should already do what you want with one small exception which was based on the requirements you originally posted in Microsoft Community. In the attached version, I have removed that exception.

Could you please take a look at it. If it doesn't do what you want, try to explain as clearly as you can for which cells in column B the coloring is wrong, and why. Also indicate what the coloring should be, and why. Thanks in advance!

DividendCalendar.xls

[bknight]

The problem is that you're explaining it the wrong way round. We cannot change today's date, but we can change the dates in column B. So you should have provided a series of examples in column B and tell us how they should be colored based on the current date.

Of course todays date can't be changed, however, every day that value changes

But if I understand you correctly, the workbook that I posted should already do what you want with one small exception which was based on the requirements you originally posted in Microsoft Community. In the attached version, I have removed that exception.

The objective hasn't changed, but I very likely described it incorrectly, my apologies for wasting time in incorrect attempts in solutions.

Could you please take a look at it. If it doesn't do what you want, try to explain as clearly as you can for which cells in column B the coloring is wrong, and why. Also indicate what the coloring should be, and why. Thanks in advance!

That works, for example change the date in B2 to 4/24/2016 and the cell is yellow, and it should be yellow as 4/24/2016-->7/9/2016 is 76 days.
Changing the date to 4/25/2016 the cell is green, and it should be green 4/25/2016-->7/9/2016 is 75 days.
Changing the date to 5/12/2016 the cell is green, and it should be green 5/12/2016-->7/9/2016 is 58 days.
Thanks for the patience and help.

DividendCalendar.xls

[bknight]

One final post and this thread is done.

There was one more requirement that I needed, but never stated. That being the color should be white if the target date was greater than todays date. That was fixed easily by adding a third condition, as the first condition and moving the former first and second conditions to second and third respectively.

[HansV]

Thanks for the feedback. Glad that your problem has been solved!