alternate rowshading using conditional format excel 2007/2010

[Ludo]

Hi,

I'm using following code on a Excel 2003 version without problems to change the row shading.
Formula comes from John Walkenbachs site see:
http://spreadsheetpage.com/index.php/tip/alternate_row_shading_using_conditional_formatting/
The code comes from the macro recorder from Excel 2003:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW();2)=0"
Selection.FormatConditions(1).Interior.ColorIndex = intColorIndex

where intColorIndex is a number from 1 to 56.

But this isn't working anymore in Excel 2007!

Anyone knowing how to code it for Excel 2007/2010?

Thanks in advance.
Ludo

 

[Peter T]

If you want something that'll be compatible with 2003/2007, instead of
ColorIndex apply an valid color-value to the Color property, eg
..FormatConditions(1).Interior.Color = RGB(255,255,0) ' eg yellow

In 2003 the RGB colour will map to the closest in the 56 colour palette.

or you could in theory do
..FormatConditions(1).Interior.Color = wb.colors(6)
where wb refers to the workbook and 6 is a colorindex 1-56.

However if you're only using 2007/2010 it's normally better to use the new
Tables and Table Styles for alternate row shading

Regards,
Peter T

 

[Ludo]

Op zondag 15 juli 2012 17:42:48 UTC+2 schreef Peter T het volgende:

If you want something that'll be compatible with 2003/2007, instead of
ColorIndex apply an valid color-value to the Color property, eg
.FormatConditions(1).Interior.Color = RGB(255,255,0) ' eg yellow

In 2003 the RGB colour will map to the closest in the 56 colour palette.

or you could in theory do
.FormatConditions(1).Interior.Color = wb.colors(6)
where wb refers to the workbook and 6 is a colorindex 1-56.

However if you're only using 2007/2010 it's normally better to use the new
Tables and Table Styles for alternate row shading

Regards,
Peter T

Hi Peter,

Thanks for the info.
The purpose is to get it working only on a 2007/2010 Excel version.

I just recorded a macro (see code below) but it's still not working.
All the rows keep on blank instead of alternate blank / red as supposed.
So, what's wrong with it?

Sub Macro3()
'
' Macro3 Macro
'
Range("A115").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW();2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255 'red
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select

End Sub

Any help welcome to get it working.

Regards,
Ludo

 

[Ludo]

Op zondag 15 juli 2012 21:52:24 UTC+2 schreef Ludo het volgende:

Op zondag 15 juli 2012 17:42:48 UTC+2 schreef Peter T het volgende:
> If you want something that'll be compatible with 2003/2007, instead of
> ColorIndex apply an valid color-value to the Color property, eg
> .FormatConditions(1).Interior.Color = RGB(255,255,0) ' eg yellow
>
> In 2003 the RGB colour will map to the closest in the 56 colour palette.
>
> or you could in theory do
> .FormatConditions(1).Interior.Color = wb.colors(6)
> where wb refers to the workbook and 6 is a colorindex 1-56.
>
> However if you're only using 2007/2010 it's normally better to use the new
> Tables and Table Styles for alternate row shading
>
> Regards,
> Peter T
>
>
Hi Peter,

Thanks for the info.
The purpose is to get it working only on a 2007/2010 Excel version.

I just recorded a macro (see code below) but it's still not working.
All the rows keep on blank instead of alternate blank / red as supposed.
So, what's wrong with it?

Sub Macro3()
'
' Macro3 Macro
'
Range("A115&quot.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW();2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255 'red
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1&quot.Select

End Sub

Any help welcome to get it working.

Just tried following (format as table - button right to the Conditional formatting button in the 2007 ribbon))

Sub Macro4()
'
' Macro4 Macro
'

'
Range("A117").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$17"), , xlNo).Name = _
"Tabel1"
Range("Tabel1[#All]").Select
ActiveSheet.ListObjects("Tabel1").TableStyle = "TableStyleLight2"
End Sub

And this seems to work.
Only, i'm not interested in the column arrows.

There seems to be a lot of changes made in the conditional formatting section.

Anyhow thanks for the help.

 

[Ludo]

<SNIP>


Hi all,

Found the problem!

I'm used to work on a English version of Excel at my work, but at home i use a Dutch Office version, witch cause the problem.

When i change the formula to the Dutch equivalent of the English one, it's working.
See below:
Sub Macro7()
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=REST(RIJ();2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

But what i don't understand now is the fact that even i write the formula in Dutch in the Conditional format form, that it isn't translated to Englishin the VBA code. This means that when i want to use this VBA code on a English machine that i have to change the formula in English to get it working..

I was convinced that writing VBA code in English could be run on any machine with a different default language.

I can't follow anymore.
Weird behaviour, or i'm missing something crucial here.

&gt; Regards,
&gt; Ludo

 

[Peter T]

I'm pleased you discovered the problem! Your first macro worked fine for me,
and was the same as my own recorded macro. One more thing to keep in mind -
in English Excel (and most other languages) you'll need a comma, not a
semi-colon

Formula1:="=MOD(ROW(),2)=0" ' note the comma

If your code needs to cater for multiple languages use -

country = Application.International(xlCountryCode)

to translate the codes see
http://support.microsoft.com/kb/213833

I was convinced that writing VBA code in English could be run on any
machine with a different default language.

VB/A is mainly US English, however there are some Object model differences
with key words and many differences concerning Excel formulas (eg MOD v.
REST). Somewhere "out there" is a workbook with formula translations in
several languages. I don't have the link but it's probably not difficult to
find it

Regards,
Peter T



<SNIP>


Hi all,

Found the problem!

I'm used to work on a English version of Excel at my work, but at home i use
a Dutch Office version, witch cause the problem.

When i change the formula to the Dutch equivalent of the English one, it's
working.
See below:
Sub Macro7()
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=REST(RIJ();2)=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

But what i don't understand now is the fact that even i write the formula in
Dutch in the Conditional format form, that it isn't translated to English in
the VBA code. This means that when i want to use this VBA code on a English
machine that i have to change the formula in English to get it working.

I was convinced that writing VBA code in English could be run on any machine
with a different default language.

I can't follow anymore.
Weird behaviour, or i'm missing something crucial here.

&gt; Regards,
&gt; Ludo

 

[Ludo]

Op zondag 15 juli 2012 23:24:28 UTC+2 schreef Peter T het volgende:

I'm pleased you discovered the problem! Your first macro worked fine for me,
and was the same as my own recorded macro. One more thing to keep in mind -
in English Excel (and most other languages) you'll need a comma, not a
semi-colon

Formula1:=&quot;=MOD(ROW(),2)=0&quot; ' note the comma

If your code needs to cater for multiple languages use -

country = Application.International(xlCountryCode)

to translate the codes see
http://support.microsoft.com/kb/213833

&gt; I was convinced that writing VBA code in English could be run on any
&gt; machine with a different default language.

VB/A is mainly US English, however there are some Object model differences
with key words and many differences concerning Excel formulas (eg MOD v.
REST). Somewhere &quot;out there&quot; is a workbook with formula translations in
several languages. I don't have the link but it's probably not difficult to
find it

Regards,
Peter T



Hi all,

Found the problem!

I'm used to work on a English version of Excel at my work, but at home i use
a Dutch Office version, witch cause the problem.

When i change the formula to the Dutch equivalent of the English one, it's
working.
See below:
Sub Macro7()
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:=&quot;=REST(RIJ();2)=0&quot;
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

But what i don't understand now is the fact that even i write the formula in
Dutch in the Conditional format form, that it isn't translated to English in
the VBA code. This means that when i want to use this VBA code on a English
machine that i have to change the formula in English to get it working.

I was convinced that writing VBA code in English could be run on any machine
with a different default language.

I can't follow anymore.
Weird behaviour, or i'm missing something crucial here.

&amp;gt; Regards,
&amp;gt; Ludo

Hi Peter,

Thanks for the additional update.

Regards,
Ludo