alternate rowshading using conditional format excel 2007/2010

  • Thread starter Thread starter Ludo
  • Start date Start date
L

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
 
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
 
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
Click to expand...
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("A1:D15").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
 
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("A1:D15").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.
Click to expand...
Just tried following (format as table - button right to the Conditional formatting button in the 2007 ribbon))

Sub Macro4()
'
' Macro4 Macro
'

'
Range("A1:D17").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.
 
<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
 
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.
Click to expand...

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
 
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
Click to expand...

Hi Peter,

Thanks for the additional update.

Regards,
Ludo
 
Back
Top