Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate Column Shading to be applied only to visible columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate Column Shading to be applied only to visible columns
Sub ColorColumns()
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 = 0 Set Rng = ActiveSheet.UsedRange Rng.Interior.ColorIndex = CI(i) For Each c In Rng.Columns If Not c.Hidden Then c.Interior.ColorIndex = CI(i) i = 1 - i End If Next c End Sub -- Jim Cone Portland, Oregon USA ( Excel add-in: http://tinyurl.com/ShadeData ) "andreashermle" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate Column Shading to be applied only to visible columns
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate Column Shading to be applied only to visible columns
On May 28, 4:16*pm, "Jim Cone" wrote:
Sub ColorColumns() 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 = 0 Set Rng = ActiveSheet.UsedRange Rng.Interior.ColorIndex = CI(i) For Each c In Rng.Columns * If Not c.Hidden Then * * *c.Interior.ColorIndex = CI(i) * * *i = 1 - i * End If Next c End Sub -- Jim Cone Portland, Oregon *USA ( Excel add-in: *http://tinyurl.com/ShadeData) "andreashermle" wrote in ... 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate Column Shading to be applied only to visible columns
On May 28, 4:51*pm, ryguy7272
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate Column Shading to be applied only to visible columns
'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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternate Column Shading to be applied only to visible columns
On May 29, 4:10*pm, "Jim Cone" 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternate to Sheet_Calc when Filter applied. | Excel Programming | |||
Alternate Row Shading (Visible Rows Only) | Excel Worksheet Functions | |||
Alternate row shading | Excel Programming | |||
Alternate Shading | Excel Discussion (Misc queries) | |||
Alternate shading based on series of numbers in 3 columns | Excel Programming |