visual basic/conditional format problem in Spanish Excel, English VB

[Mike]

Hello,

I'm using Excel 2000 sp3, and i have some code that tries to do this (and
generally succeeds):

With rngTarget.FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=if(or(" & rngTargetTopCell.Address(False, False) &
strOperator _
& rngRed1.Address(False, False) & "," &
rngTargetTopCell.Address(False, False) _
& strOperator & rngRed2.Address(False, False) &
"),True,False)")

However, when someone at the company plant in Spain tries to run the code
(in Excel XP or whatever it's called, Excel 2003 maybe?), when it hits the
above line, error.

After a long slow debugging session over the internet between myself (not a
Spanish communicator) and the user in Spain (who speaks decent English but
knows nothing about visual basic, programming, or most excel worksheet
functions, I discovered that instead of trying to put in the conditional
format of
"=if(or(w3>u3,w3>t3),true,false)", I have to somehow get visual basic to put
in
"=si(O(w3>u3;w3>t3),verdadero,falso)" for the conditional format (not only
are the formulas different as one might expect but semicolons instead of
commas between the Or/O function arguments)

Yet I also know that if I send them a file where the formatting was done in
english, it will translate to spanish - how do I get VB to do that on the
fly?

Anyone have any help here?

(please reply to the ng)

 

[Bob Phillips]

I can't test it, but try this little tweak

Dim sFormula As String

Cells(Rows.Count, Columns.Count).Formula = _
"=IF(OR(" & _
rngTargetTopCell.Address(False, False) & strOperator & _
rngRed1.Address(False, False) & "," & _
rngTargetTopCell.Address(False, False) & _
strOperator & rngRed2.Address(False, False) & "),True,False)"
sFormula = Cells(Rows.Count, Columns.Count).FormulaLocal
Cells(Rows.Count, Columns.Count).ClearContents
With rngTarget.FormatConditions.Add( _
Type:=xlExpression, _
Formula1:=sFormula)


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Tom Ogilvy]

VBA won't do that.

You can add a blank sheet and do the conditional formatting formula as a
regular formula:

select the same cell,

activecell.formula = "=if(or(" & rngTargetTopCell _
.Address(False, False) & strOperator _
& rngRed1.Address(False, False) & "," & _
rngTargetTopCell.Address(False, False) _
& strOperator & rngRed2.Address(False, False) & _
"),True,False)")

sform = ActiveCell.FormulaLocal