VBA: conditional formatting R1C1 formulas Hi, I\'m writing a VBA code with condi

Question

VBA: conditional formatting R1C1 formulas

Hi,

I'm writing a VBA code with conditional formatting containing an "AND" formula.

However, depending on the country or language settings of the excel's user, the code stops working.

I've tried to use R1C1 references, but unfortunately without success.

Please find it below part of the code I've written:

Range(Cells(7, 9), Cells(7 + ((Range("e4")) - 1), 9 + (x + iUltDia - 1))).Select 'I7:(I+auxiliares)(7+numero de inputs)

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

        "=AND(R4C[0]>=R[0]C5;R4C[0]<=

'select a pre-determined range of cells and apply the formula above to it

Any suggestions on how to solve this?

Thanks a lot!!

Explanation / Answer

.,Hello

I wonder if it is a matter of ; instead of ,   *   (plus the missing brackets at the end)

With my regional settings fda

selection.formula="=SUM(A1,A2)"      '----> OK and tranforms it automatically to function and work correctly.

selection.formulaLocal="=SUM(A1;A2)"      '---> OK

selection.formula="SUM(A1;A2)"     ' XXX ERROR

although i would write in excel =sum(a1;A2)

In theory, when using .Formula then Excel translates it to FormulaLocal according to regional and language settings (or atleast tha't what the user sees). But i cannot tell how will things behave when there is translation included :(

This formula application.International( Index ) will provide you some info about regional settings of the computer your program runs, and perhaps you can write functions accordingly.

ex: Print application.International(xlNonEnglishFunctions)   --> False that means that I am displaying functions in English

Hope I've been more helpful than frustrating...

Cheers ^_^

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.