Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. > Regards, > Ludo |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. > Regards, > Ludo |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:="=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 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 Hi Peter, Thanks for the additional update. Regards, Ludo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro alternate conditional format change | Excel Programming | |||
Conditional Format Excel 2007 | Excel Discussion (Misc queries) | |||
Conditional Format Excel 2007 | Excel Programming | |||
Conditional Format - Excel 2007 | Excel Discussion (Misc queries) |