This has got me scratching my head and I guess I could use some help please.
Working in Excel 2003, I have written a Function as follows:
Function IsFormula(rng As Range)
If rng.HasFormula = True Then IsFormula = True
Then I call this function as the first (of 3) critera in a conditional format as follows:
That conditional format is applied appropriately for columns A through AL and Rows 5 through 72 (e.g.
I have buttons to which I have attached AutoFilter code. Here is an example:
Selection.AutoFilter Field:=1, Criteria1:="Open*", Operator:=xlOr, Criteria2:="=Pending"
Now the head scratching bits:
If I manually apply the filter, the conditional format works fine
If I run the AutoFilter code, I get a #VALUE! error in cells where other Custom Functions are called.
HOWEVER, if I force a full calc (CTRL+ALT+SHIFT+F9) the custom functions are calculated and the conditional formats are
I have stepped through the code line by line and have noticed the AutoFilter subs never actually exit the AutoFilter step.
The AutoFilters are applied, but the code stops on that line. Using the above example, the "Range" and "SendKeys" lines are
If I remark out the IsFormula function, the AutoFilter buttons work fine (but the conditional formats which use the IsFormula
function do not format correctly) and the other custom functions work fine.
So I came up with a formula to use in the conditional format in place of the function as follows:
Using this formula (and making sure that all references to the IsFormula Function are replaced), everything works fine.
All the AutoFilters work and all the conditional formats complete and all fields are correct.
So while the issue is technically resolved, I would still like to understand why the Function method for the conditional
format only works when I manually force a full calc of the sheet or manually apply the filters.
I am reasonably sure that the #VALUE! errors in the other custom functions are simply an artifact of the sort functions not
properly exiting their respective Subs because all of them return the #VALUE! error AND they all work fine when the first
conditional format is changed from the function to the formula.
Again, any and all help will be appreciated.