Bob Phillips' CFMet VB/formula) returning #value error in 2007 that it didn't in 2003

[James Bowman]

Hello everyone,

Long time listener first time caller.

My office has just changed to office 2007 from 2003 and it has caused one of my spreadsheets to stop working.

It is a large spreadsheet filled with conditional formatting (of all kinds e.g. values and types of formulae). The conditional formatting highlights any cells that have a value (could be a number, date or string) that does not match with other values. We only need the rows with >= 1 highlighted cell.

I have been using Bob Phillips CFMet vb and formula successfully in excel 2003 (see: http://www.xldynamic.com/source/xld.CFConditions.html#isitmet)
Though I must admit I couldn't get the range part further down to work so have been using an IF(OR(CFMet(a1),CFMet(B1),<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:stockticker>TRUE</st1:stockticker>,FALSE) statement.

The issue that has reaked havoc is that since changing to excel 2007 the formulae (CFMet) is returning a #VALUE! error for any cell that has conditional formatting set, even if it is not satisfied.

Needless to say this has thrown a spanner in our integrity checks so any help will be greatly appreciated.

Cheers,

James B

 

[James Bowman]

Sorry everyone just learning my forum rules and etiquitte. This thread is also on http://www.ozgrid.com/forum/showthread.php?t=144306 and http://www.excelforum.com/excel-pro...ue-error-in-2007-that-it-didnt-in-2003-a.html

Please view these before replying. I will post any solution on all three sites. Sorry for the hassle