Alternate Column Shading to be applied only to visible columns
in [Programming]
|
Prev: VB converting UK to US dates, randomly, ignores cdate func!
Next: NumberFormat not getting changing onFly changing the regional sett
From: andreashermle on 28 May 2010 08:34 Dear Experts: Below macro applies alternate shading to the used range considering ONLY visible rows (alternate row shading to visible rows). Could somebody please re-write the code to perform COLUMN banding (alternate column shading) instead of row banding (alternate row shading) . Only visible columns should be considered. Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub ColorRows() Dim c As Range Dim CI(0 To 1) As Long Dim i As Long Dim Rng As Range CI(0) = xlColorIndexNone CI(1) = 15 'I actually changed this from her's for light grey i = 0 Set Rng = ActiveSheet.UsedRange Rng.Interior.ColorIndex = CI(i) For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible) Rng.Rows(c.Row).Interior.ColorIndex = CI(i) i = 1 - i Next c End Sub
From: ryguy7272 on 28 May 2010 10:51 Try replacing the two lines you have with these two lines: For Each c In Rng.Columns("A:IV").SpecialCells(xlCellTypeVisible) Rng.Columns(c.Column).Interior.ColorIndex = CI(i) -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "andreashermle" wrote: > Dear Experts: > > Below macro applies alternate shading to the used range considering > ONLY visible rows (alternate row shading to visible rows). > > Could somebody please re-write the code to perform COLUMN banding > (alternate column shading) instead of row banding (alternate row > shading) . Only visible columns should be considered. > > Help is much appreciated. Thank you very much in advance. > Regards, Andreas > > > Sub ColorRows() > Dim c As Range > Dim CI(0 To 1) As Long > Dim i As Long > Dim Rng As Range > > > CI(0) = xlColorIndexNone > CI(1) = 15 'I actually changed this from her's for light grey > i = 0 > > Set Rng = ActiveSheet.UsedRange > Rng.Interior.ColorIndex = CI(i) > > For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible) > Rng.Rows(c.Row).Interior.ColorIndex = CI(i) > i = 1 - i > Next c > End Sub > . >
From: andreashermle on 29 May 2010 02:01 On May 28, 4:51 pm, ryguy7272 <ryguy7...(a)discussions.microsoft.com> wrote: > Try replacing the two lines you have with these two lines: > For Each c In Rng.Columns("A:IV").SpecialCells(xlCellTypeVisible) > Rng.Columns(c.Column).Interior.ColorIndex = CI(i) > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''.. > > > > "andreashermle" wrote: > > Dear Experts: > > > Below macro applies alternate shading to the used range considering > > ONLY visible rows (alternate row shading to visible rows). > > > Could somebody please re-write the code to perform COLUMN banding > > (alternate column shading) instead of row banding (alternate row > > shading) . Only visible columns should be considered. > > > Help is much appreciated. Thank you very much in advance. > > Regards, Andreas > > > Sub ColorRows() > > Dim c As Range > > Dim CI(0 To 1) As Long > > Dim i As Long > > Dim Rng As Range > > > CI(0) = xlColorIndexNone > > CI(1) = 15 'I actually changed this from her's for light grey > > i = 0 > > > Set Rng = ActiveSheet.UsedRange > > Rng.Interior.ColorIndex = CI(i) > > > For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible) > > Rng.Rows(c.Row).Interior.ColorIndex = CI(i) > > i = 1 - i > > Next c > > End Sub > > .- Hide quoted text - > > - Show quoted text - Dear Ryan, thank you very much for your kind help. I am afraid to tell you that I am getting erroneous results, that is too many columns are getting shaded not only the used range. I tried Jim's one, that one is working. Anyway, thank you very much for your professional help. Regards, Andreas
From: Jim Cone on 29 May 2010 10:10 'One more time... '--- Sub ColorColumns_R1() 'Jim Cone - Portland, Oregon USA - May 2010 'Shades every other column in used range (skips hidden columns). 'Uses specified RGB color for the shade color. Dim bColor As Boolean Dim Rng As Range Dim N As Long Dim C As Long C = RGB(204, 204, 100) '<<< change to please Set Rng = ActiveSheet.UsedRange Rng.Interior.ColorIndex = xlColorIndexNone For N = 1 To Rng.Columns.Count If Rng.Columns(N).Hidden Then 'skip ElseIf Not bColor Then Rng.Columns(N).Interior.Color = C bColor = True Else bColor = False End If Next 'N Set Rng = Nothing End Sub -- Jim Cone Portland, Oregon USA Review of 'Special Sort' Excel add-in at... http://www.contextures.com/excel-sort-addin.html "andreashermle" <andreas.hermle(a)gmx.de> wrote in message... Hi Jim, great, thank you very much for your professional help. It works as desired. There is one thing I would like to ask you. How would this code have to be changed if I wanted to use RGB values as cell fill (such as RGB (224, 224, 224)) Regards, Andreas
From: andreashermle on 30 May 2010 08:04
On May 29, 4:10 pm, "Jim Cone" <james.cone...(a)comcast.netXXX> wrote: > 'One more time... > '--- > Sub ColorColumns_R1() > 'Jim Cone - Portland, Oregon USA - May 2010 > 'Shades every other column in used range (skips hidden columns). > 'Uses specified RGB color for the shade color. > Dim bColor As Boolean > Dim Rng As Range > Dim N As Long > Dim C As Long > > C = RGB(204, 204, 100) '<<< change to please > Set Rng = ActiveSheet.UsedRange > Rng.Interior.ColorIndex = xlColorIndexNone > > For N = 1 To Rng.Columns.Count > If Rng.Columns(N).Hidden Then > 'skip > ElseIf Not bColor Then > Rng.Columns(N).Interior.Color = C > bColor = True > Else > bColor = False > End If > Next 'N > Set Rng = Nothing > End Sub > -- > Jim Cone > Portland, Oregon USA > Review of 'Special Sort' Excel add-in at... > http://www.contextures.com/excel-sort-addin.html > > "andreashermle" <andreas.her...(a)gmx.de> > wrote in message... > Hi Jim, > great, thank you very much for your professional help. It works as > desired. There is one thing I would like to ask you. > How would this code have to be changed if I wanted to use RGB values > as cell fill (such as RGB (224, 224, 224)) > > Regards, Andreas Hi Jim, great job. Thank you very much for your professional help. Regards, Andreas |