Multilanguage translation
 

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references have
different names in the different countries. How can I easily adapt a
script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

Hi Simon

Can you send me a simple test workbook(private) that will blow in a German version
I go install a German version today on a Virtual PC to test this

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Simon" wrote in message oups.com...
Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references have
different names in the different countries. How can I easily adapt a
script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

replace with english R1C1 references and you should be fine.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

That's I was thinking also

Thanks for confirm it keepITcool
No need for installing the German version then for me

--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message ft.com...


replace with english R1C1 references and you should be fine.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

I don't think you have to.
If I use the Multilingual option it translates automatically, I assume (not
guarantee!) that happens with standard versions as well.
Otherwise, use the A1 reference style.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Simon" wrote in message
oups.com...
Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references have
different names in the different countries. How can I easily adapt a
script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

I tested it also with functions in the strings..
although localized functions and localized R1C1 are accepted...

I think you should stick to A1 references & functions in usenglish)
Relative refs will be correct if written for for the first cell in
range.


With Range("A6:A100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", xlR1C1, xlA1, , .Cells(1))
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
..Cells(1))

With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

Excel translates functions and reference styles in conditional formats just
as it does worksheet formulas.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"keepITcool" wrote in message
ft.com...

I tested it also with functions in the strings..
although localized functions and localized R1C1 are accepted...

I think you should stick to A1 references & functions in usenglish)
Relative refs will be correct if written for for the first cell in
range.


With Range("A6:A100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", xlR1C1, xlA1, , .Cells(1))
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
.Cells(1))

With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Simon wrote :

Hi,
I need to adapt a German VB script used in Excel into a version which
will work in Europe. The problem is that the Line/Colon references
have different names in the different countries. How can I easily
adapt a script into a multilanguage version?

Here an extract of the German Version with ZS references:

'Mise en forme conditionnelle première colonne
With Range("A6:A" + CStr(Range("LastLine").Value + 5))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=ZS6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(ZS6<1)*(ZS2+ZS3<=Z4S2)*(ZS2<"""")"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(Z4S2=ZS2)*(ZS2+ZS3=Z4S2)*(ZS2<"""")"
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

hmm.. s't flawed in copy.paste :)

Sub x()
'Add Conditional formatting
With Range("A6:A100")

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=RC6=100%", _
xlR1C1, xlA1, , .Cells(1))
.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", _
xlR1C1, xlA1, , .Cells(1))
.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", _
xlR1C1, xlA1, , .Cells(1))

With .FormatConditions(1).Font
.Strikethrough = True
End With
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With

End With
End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :


I tested it also with functions in the strings..
although localized functions and localized R1C1 are accepted...

I think you should stick to A1 references & functions in usenglish)
Relative refs will be correct if written for for the first cell in
range.


With Range("A6:A100")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC6=100%"
.FormatConditions(1).Font.Strikethrough = True
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"RC6<1)*(RC2+RC3<=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
.Cells(1)) With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($B$4=$B6)*($B6+$C6=$B$4)*($B6<"""")"
'.FormatConditions.Add Type:=xlExpression, Formula1:= _
Application.ConvertFormula( _
"=(R4C2=RC2)*(RC2+RC3=R4C2)*(RC2<"""")", xlR1C1, xlA1, ,
.Cells(1))

With .FormatConditions(3).Font
.Bold = True
.Italic = False
.Strikethrough = False
.ColorIndex = 5
End With
End With

Multilanguage translation
 

niek..

true .. ONCE you have a working formula.

when setting the formula:

in cells you can choose to assign
US/Local A1/r1c1
and with

in formatconditions you cant...
(excel will do it's best to get it right,
attempting USenglish/a1 first

similar to
Cells(1).VALUE = "=sum(a1:a100)"





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Niek Otten wrote :

Excel translates functions and reference styles in conditional
formats just as it does worksheet formulas.

Multilanguage translation
 

Hi everybody,
thanks for your contribution to solving my problem. There is no more
multilanguage problem, thanks a lot!

*** Sent via Developersdex http://www.developersdex.com ***