FormatConditions Formula1 differences between 2003 and 2007

[Luis Garcia]

Step1: Select range 'A1:B5'
Step2: Fill with numbers
Step3: Apply this FormatCondition:
Formula '=A1=2', with Red Font.

Then, all cells with contains number 2 are Red.

Step4: Select cell 'B2'

In VBA, Excel 2003:
Msgbox ActiveCell.FormatConditions(1).Formula1 shows:
'=B2=2'

In VBA, Excel 2007:
Msgbox ActiveCell.FormatConditions(1).Formula1 shows:
'=A1=2'

I have a Worksheet with ALL depends of this behavior!!!
I evaluate '.Formula1' for each cell in range, but in XLS2007 evaluates wrong values!!!

I need Help

 

[lylyana]

You can use this:
acell = ActiveCell.Address
ifor = Range("B1").Address
Formula_res = Application.ConvertFormula(Application.ConvertFormula(ActiveSheet.Range(acell).FormatConditions(1).Formula1, xlA1, xlR1C1, , Range(ifor)), xlR1C1, xlA1, xlAbsolute, ActiveSheet.Range(acell))


Where acell is your activecell, and ifor is the initial cell where you aplicate the formula for all your range of format condition (at the sample you give is A1).

With this, formula_res will give you the formula of the conditional format for your activecell.

Hope this help.

 

[Luis Garcia]

Works fine.

Thank you very much Lylyana