Problems using FormatConditions.Add with an Expression

[John]

I'm trying to get conditional formatting working in Excel 2007 using VB.NET
2005, and I can't figure out how to get the expression to work so that it's
relative to the current cell. I've tried using A1 notation, R1C1 notation,
selecting the range I'm putting the formatting on. Everything I've tried so
far results in the exact same expression being used in all cells where the
formatting is applied. Specifically, I'm trying to add formatting to a range
that is something like M15:O200. My statement right now is something along
the lines of this:

valuesRange.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, ,
"=AND(RC[-1]=""DELTA"", RC=0)")

So in M17, for example, I'd like the formulat to be "=AND(L17="DELTA",M17=0)"

However, in all cells in the entire range, the formula always says
"=AND(L15="DELTA",M15=0)"

To make matters more confusing, it looks like the formula actually is
working in column M (not in N or O). For example, if I look at the formula
in M20, for example, it says "=AND(L15="DELTA",M15=0)", but it's being
evaluated as if it's actually checking cells L20 and M20.

Can anyone shed any light on this. I've searched all over, and can't find
any documentation on how cell references work within the expression when
doing this.

Thanks!

 

[Bob Phillips]

It all depends where the active row is as CF adjusts the formula according
to the active row. So if you are in row 16, and you are setting the formula
for a range starting at row 17, use 16 not 17 (doesn't seem to make sense,
but it does).

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[John]

The thing I can't figure out, is how to put a different formula in each cell.
My range is M15:O200.

If I do this:

valuesRange.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, ,
"=AND(A1=""DELTA"", B2=0)")

That puts this formula in: =AND(M15="DELTA", N16=0)

That aligns with what you're saying, and makes sense... the problem is,
every cell in my range gets that same formula. How do I make it so that M15
gets the following:
=AND(M15="DELTA", N15=0)

while M16 gets this:
=AND(M16="DELTA", N16=0)

That's what I can't seem to grasp.

Thanks!

 

[John]

Ok,

On further reflection, I understand better what you're saying. I didn't
understand that CF applies the same formula to the whole range in Excel 2007.

This formula actually gives me exactly the behavior I want, if I just type
it into Excel: =AND($L15="DELTA",M15=0)

I just need to figure out how to apply that using FormatConditions.Add

Thanks,
John

 

[Jim Thomlinson]

Not sure if theis helps but...

http://support.microsoft.com/kb/895562

 

[John]

Thanks folks. I understand how this works, and got it working now.

 

[IanWright]

If you still have it can you please post your Solution.