Conditional formatting based on the file save date

Whether this can be done with conditional formatting depends on what your data looks like. Does the data include dates of any kind, that would be compared to the save date to determine formatting? The question is kind of vague to provide specific guidance; can you attach a sample workbook?

Sorry it has been so long for me to get back to this.

You have a conditional format rule for column K but I don't understand what you are trying to do with it. The rule returns a number, but conditional formatting rules need to return TRUE or FALSE. And there is no formatting specified for the rule. The rule is:

=IF($E$2="Equipment/Pipeline",0,IF($E$2="PPM Nation",25,50))

Now, as for that date. You have a UDF to return last saved date. This function does not reference a cell. By default, Excel only recalculates a function when a referenced cell is updated. To change the default, you must include the following statement in the function:

Please Login or Register  to view this content.

This will cause the function to recalculate if any cell changes.

This may solve your problem regarding providing the correct date. You still need to fix your conditional formatting. First you should name the range Data!F28 to something like LastSave, because conditional formatting rules cannot reference cell addresses on another sheet, but it can reference named ranges.

I don't know which date you care about but I'll assume it's Date Flagged. The format associated with this rule should be red text:

=$D2>=LastSave

However, be aware that this may not actually solve your business problem. What you are proposing will simply mean that someone opens the file, adds a few new items, they look red, then as soon as they save it they turn black again. That is exactly what you've described but doesn't sound very useful to me. It all depends on how you're going to use this.

What you may really need is the ability to make all new entries red until you hit some kind of "reset" button, rather than trying to compare to the last save date. Is this file on a network drive and accessed by multiple people? Or are there multiple people, each with their own copy? Or just one person? I would need to do a quick business analysis of your overall process to be able to give you better advice.

P.S. Since you have taken the step to write code, there is an alternative to your UDF. Another option is to put this code in the Workbook module:

Please Login or Register  to view this content.

This will update the desired cell each time the workbook is saved, without having to worry about a volatile function being called every time you update anything.