FormatConditions Formula1 differences between 2003 and 2007

Luis Garcia

New Member
Joined
Jun 8, 2010
Messages
3
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 ;)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

lylyana

New Member
Joined
Jun 30, 2010
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,288
Messages
5,577,212
Members
412,776
Latest member
nadroy87
Top