Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default alternate rowshading using conditional format excel 2007/2010

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...al_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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 84
Default alternate rowshading using conditional format excel 2007/2010

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" wrote in message
...
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...al_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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default alternate rowshading using conditional format excel 2007/2010

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.FormatConditi ons.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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default alternate rowshading using conditional format excel 2007/2010

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.FormatConditi ons.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.

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.

Regards,
Ludo


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default alternate rowshading using conditional format excel 2007/2010


<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.FormatConditi ons.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



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 84
Default alternate rowshading using conditional format excel 2007/2010

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


"Ludo" wrote in message
...

<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.FormatConditi ons.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

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default alternate rowshading using conditional format excel 2007/2010

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.FormatConditi ons.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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro alternate conditional format change Monk[_2_] Excel Programming 0 February 12th 10 09:16 PM
Conditional Format Excel 2007 Marilyn Excel Discussion (Misc queries) 3 May 14th 09 08:46 PM
Conditional Format Excel 2007 Sue Excel Programming 2 December 23rd 08 11:01 AM
Conditional Format - Excel 2007 6538[_2_] Excel Discussion (Misc queries) 1 March 2nd 08 09:34 AM


All times are GMT +1. The time now is 09:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"