VBA - default contents of a cell based on numerical value of another

Hi Guys,

I'm pretty new to VBA and am trying to make something work...

My code is below; it does not work.

This is probably because the syntax is wrong, but hopefully you can see what I'm trying to do...

I want the cell in column F to read "Cold" if the cell to the left of it (column E) has a date in it which is over 90 days old.

If its under 90 days, then i want it to do nothing as there is a data validation drop down box.

Please Login or Register  to view this content.

Hi,

Are you certain you actually need a macro? Where's the Data validation? Is that in F1 and other column F cells.

What event should trigger this macro? At the moment it will fire whenever any cell on the sheet is changed.
I'm guessing that when you pick a value from a dropdown in column F, then, and only then if the date is older than 90 days do you wish to change the data validation to Cold irrespective of what might have been selected in the drop down.

If so then use

Please Login or Register  to view this content.

Otherwise upload the workbook.

Password on the file is "Miami"

Try this in column F:

=IF(TODAY()-90>E29,"Cold","")

The formula solution won't work if you are still able to overwrite it with a selection from a drop down list, so it looks like Richard's vba suggestion is better.

He posted a couple of questions about how you want to trigger the change.

Originally Posted by jjjjjjjjunit

Richards solution is what I was gunning for, though I put it into my worksheet and it didn't work; looking at it, it doesn't factor in column E at all (which is where the date is..

Richard: Dates are in column"E" and data validation is in column "F". I basically want each celling in "F" to default to "Cold" when its neighbouring cell in "E" contains a date that is over 90 days.

that make sensE?

It does factor in column E although it's not immediately obvious. The macro fires when you make a change in column F. The code

Formula:

Please Login or Register  to view this content.

test to see if the cell relative to the target on the same row - that's the default "," in the parentheses, and in the column to the left, that's the "0" is 90 days ago or more. i.e. the cell one cell to the left of an F cell is an E cell.

Originally Posted by Richard Buttrey

Hi,

Are you certain you actually need a macro? Where's the Data validation? Is that in F1 and other column F cells.

What event should trigger this macro? At the moment it will fire whenever any cell on the sheet is changed.
I'm guessing that when you pick a value from a dropdown in column F, then, and only then if the date is older than 90 days do you wish to change the data validation to Cold irrespective of what might have been selected in the drop down.

If so then use

Please Login or Register  to view this content.

Otherwise upload the workbook.

Hi Richard,

The above script gives me the desired outcome, but it is triggered in the wrong way. I need it to be automated, so that as soon as a date becomes older than 90 days, it triggers the even (change value of adjacent cell ("E") to "cold").

In its current format, it merely restricts the user entry options to "cold", but the user still has to actively attempt to change the value in that cell.

any suggestions? I can't seem to crack it.

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)

I've change it now for you.
People here are not attracted by complexity but by challenge. Proper title can narrow interested solvers much better.

I see.. I was testing it incorrectly...

This now works when I change the date. I suppose the only way to test if its automated is by setting some dates to 89 days and waiting until tomorrow?

Also, is there a way I can add a caveat to this? i.e. If "F" = "do not contact" the do nothing?

You can certainly add a caveat. I fear I may have misjudged your requirement though. If you require the colouring to be 'live' so to speak, you will need to determine what should trigger the changes. I suspect the Workbook_Open event would also be necessary. To that end, add this code to a normal module:

Please Login or Register  to view this content.

You may then alter the earlier Worksheet_Change code to simply this:

Please Login or Register  to view this content.

and lastly change the Workbook_Open code to use the new routine as well:

Please Login or Register  to view this content.