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" <(E-Mail Removed)>
wrote in message news:8c3ba7e5-8007-42e1-bf30-(E-Mail Removed)...
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" <james.cone...@comcast.netXXX> 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" <andreas.her...@gmx.de>
> wrote in messagenews:8c3ba7e5-8007-42e1-bf30-(E-Mail Removed)...
> 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 <ryguy7...@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

'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" <(E-Mail Removed)>
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" <james.cone...@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...@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