Help with VBA code to run code when a cell value increases

[ExcelFailure]

Hi there,

After some help on the following code:

Please Login or Register  to view this content.

In A1 I have a countif formula which counts the dates in a range that are > than tomorrow.

This code works intermittantly it seems but not all the time?

[stnkynts]

I could be wrong but I don't think there is an event that can detect a change in a formula. You might have to directly compare the dates in question to tomorrows date.

[ExcelFailure]

I don't think I am wanting to detect a change in the formula, it's more the change in the result of the formula?

[ExcelFailure]

To be clear, I'm wanting a message box to appear when a date is entered in a range that is beyond tomorrow's date, or similarly if a date is changed to one beyong tomorrow's date

[stnkynts]

I don't think I am wanting to detect a change in the formula, it's more the change in the result of the formula?

Correct. I don't think that is possible. I could be wrong though. It is however easy to compare a change in a cell which is reference in the formula. Need more info on that though.

[Alf]

Not sure I understands you problem properly but you could try this:

Right click on sheet tab where formula cell is. For instance sheet1 and past this code.

Please Login or Register  to view this content.

Then right click on “ThisWorkbook” and paste this code

Please Login or Register  to view this content.

Save file and open it again. Now try to update you formula target cell and see if MsgBox is activated.

Alf

[ExcelFailure]

Thanks for your replies, Alf, that works except it brings up the msgbox even if the result of the countif formula doesn't change?

[ExcelFailure]

stnkynts all I'm trying to do is make a msgbox appear when a user enters or changes a date in a column that is after tomorrow's date, maybe this isn't the simplest way?

[Alf]

I've tested a bit and it seems that "COUNTIF" is a special case as for instance "SUM" and "COUNT" works as exspected that is MsgBox is only activated if the result in target cell changes. Pehaps you could use another function instead of "COUNTIF"?

Alf