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

James Bowman

New Member
Joined
Jul 13, 2010
Messages
2
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-com:eek:ffice: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:confused:
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,593
Members
412,330
Latest member
carlosjw
Top