I'm trying to apply a CF rule that highlights a single cell with red fill if the date in the cell is not equal to today. Note that the cell in question calls a User-defined function. This function references the last save date of another excel workbook and displays the referenced date as mm/dd/yyyy.
When I tried doing this the most straightforward way, using the conditional formatting option from home tab >Highlight cells rules>A date occurring... I found that I could format the cell to highlight green if it is equal to today, but not the opposite condition; because 'dates occurring' pre-defined CF rule only evaluates if the date is the same as a relative date from a limited list of options such as 'Yesterday' 'Tomorrow' 'Today'.
Even if I programmed as many cell rules to highlight the cell in red for all the relative dates from this pre-defined list, it would only highlight in red certain dates that the cell's result is not equal to. I need it to highlight in red all dates not equal to today.
I've tried programming the CF rule based on formulas, or based on the rule Format only cells that contain>Cell value>not equal to>=TODAY(), but when I do this, the rule always evaluates to true and always highlights the cell in red even if the date is equal to today. I deduce this is because the CF rule is not evaluating the date resulting from the UDF formula in the cell, but rather the actual formula, which is not equal to a date.
Any help?
Here is a redacted version of the code for my UDF function, if it helps.
I have `Optional VolatileParameter As Variant` in the parameter definition so that I can call NOW() when I call the function and make it volatile so that it autoupdates constantly and importantly as soon as the workbook is opened.Please Login or Register to view this content.
Bookmarks