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

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

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
.

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

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

'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

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