Relative Cell References in Conditional Formatting

1
votes

Assume I have an Excel 2007 sheet with the following data in column A:

  1. word
  2. word2
  3. other3
  4. word

Assume I then apply this formula to the range: =IF(COUNTIF(A:A,A1)=1,0,1).

This results in returning 1 if a cell matches any other cell in the column. If I used this formula in conditional formatting and apply it to all four cells, then the cells in rows 1 and 4 above will be formatted. Excel automatically adjusts A1 in the formula to A2, A3 or A4, since it is a relative cell reference with out the '$' signs.

However, when I test this formula with the Application.Evaluate function, it always evaluates against cell A1, no matter what range I am using.

For example, if I get the range of a single cell, say cell A3 and I evaluate the formula from the cell.FormatConditions property of that cell, it will evaluate to 1, even though it does not match any other cell in the column.

Can the Evaluate function not properly use relative cell references?

1
c#excel

1 Answers

2
votes

See http://www.decisionmodels.com/calcsecretsh.htm for information on evaluate limitations

Relative references in the Evaluate string are treated as absolute, unless they are contained in defined names in which case the defined name is evaluated with respect to cell A1.